﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using ComExcel = Microsoft.Office.Interop.Excel;
using Bussiness.Manager;
using Bussiness.Entity;
using AF_ORM.CORE;
using System.Collections;
using System.Threading;
using log4net;
namespace DSC
{
    public partial class ucReport : AfUserControl
    {
        public ucReport()
        {
            InitializeComponent();

            List<ServiceType> lst = ENUM.report_SelectAll();
            cmbBaoCao.DataSource = lst;
            cmbBaoCao.DisplayMember = "text";
            cmbBaoCao.ValueMember = "value";
        }
        private void BC_DanhSachKhachHangDenSuaXe()
        {
            DataTable dt = PT_ServiceBillManager.CreateInstant().ReportByBillNo(fromDate2.Value, toDate2.Value);
            dt.Columns.Add("STT");
            DataRow dr = dt.NewRow();
            dr["STT"] = "Tổng";
            decimal Tong_LuotRap = 0;
            decimal Tong_LuotKTDK = 0;
            decimal Tong_LuotSuaChuaNgoai = 0;
            decimal Tong_TienCong = 0;
            decimal Tong_LaiGCN = 0;
            decimal Tong_LaiPTTM = 0;
            decimal Tong_TienNhot = 0;
            decimal Tong_TienPTThayThe = 0;
            decimal Tong = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i]["STT"] = (i + 1).ToString();
                decimal luotrap = int.Parse(dt.Rows[i]["SLRapBan"].ToString());
                decimal luotktdk = int.Parse(dt.Rows[i]["SLKTDK"].ToString());
                decimal luotsuachua = int.Parse(dt.Rows[i]["SLSuaChua"].ToString());
                if (luotrap >= 1)
                {
                    Tong_LuotRap += 1;
                    Tong_LuotKTDK += 0;
                    Tong_LuotSuaChuaNgoai += 0;
                }
                else if (luotktdk >= 1)
                {
                    Tong_LuotRap += 0;
                    Tong_LuotKTDK += 1;
                    Tong_LuotSuaChuaNgoai += 0;
                }
                else if (luotsuachua >= 1)
                {
                    Tong_LuotRap += 0;
                    Tong_LuotKTDK += 0;
                    Tong_LuotSuaChuaNgoai += 1;
                }
                Tong_LuotSuaChuaNgoai += 0;
                Tong_TienCong += decimal.Parse(dt.Rows[i]["TienCong"].ToString());
                Tong_LaiGCN += decimal.Parse(dt.Rows[i]["LaiGCN"].ToString());
                Tong_LaiPTTM += decimal.Parse(dt.Rows[i]["LaiPTTM"].ToString());
                Tong_TienNhot += decimal.Parse(dt.Rows[i]["TienNhot"].ToString());
                Tong_TienPTThayThe += decimal.Parse(dt.Rows[i]["TienPTThayThe"].ToString());
                Tong += decimal.Parse(dt.Rows[i]["TongCong"].ToString());
            }
            dr["SLRapBan"] = Tong_LuotRap.ToString();
            dr["SLKTDK"] = Tong_LuotKTDK.ToString();
            dr["SLSuaChua"] = Tong_LuotSuaChuaNgoai.ToString();
            dr["TienCong"] = Tong_TienCong.ToString();
            dr["LaiGCN"] = Tong_LaiGCN.ToString();
            dr["LaiPTTM"] = Tong_LaiPTTM.ToString();
            dr["TienNhot"] = Tong_TienNhot.ToString();
            dr["TienPTThayThe"] = Tong_TienPTThayThe.ToString();
            dr["TongCong"] = Tong.ToString();
            dt.Rows.Add(dr);
            saveFileExcel.FileName = "BC1_DanhSachKhachHangDenSuaXe_"+DateTime.Now.ToString("dd_MM_yyyy_HH_mm")+".xls";
            saveFileExcel.DefaultExt = "xls";
            saveFileExcel.Filter = "Excel 2003 Files(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx|All Files|*.*";
            saveFileExcel.AddExtension = true;
            saveFileExcel.RestoreDirectory = true;
            saveFileExcel.Title = "Chọn đường dẫn lưu file";
            saveFileExcel.InitialDirectory = @"C:/";
            if (saveFileExcel.ShowDialog() == DialogResult.OK)
            {
                if (saveFileExcel.FileName.Split('\\')[saveFileExcel.FileName.Split('\\').Length - 1] == ".xls")
                {
                    MessageBox.Show("Nhập tên file cần lưu");
                }
                else
                {
                    // Khoi dong Excel
                    ComExcel.Application exApp = new ComExcel.Application();

                    //// them file temp xls moi
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);

                    // Mo mot file Excel co san
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);
                    ComExcel.Workbook exBook = exApp.Workbooks.Add(Application.StartupPath + "/ExcelTemplate/BC1.xls");

                    // Lay sheet 1
                    ComExcel.Worksheet exSheet = (ComExcel.Worksheet)exBook.Worksheets[1];
                    //exSheet.Activate();

                    // dia chi 
                    DataTable headdt = PT_ServiceBillManager.CreateInstant().GetReportHead(SessionLogin.UserId).Tables[0];
                    exSheet.Cells[3, 1] = headdt.Rows[0]["HEAD_ADD"];

                    //tu ngay- den ngay - dong 6 , cot 9
                    exSheet.Cells[6, 1] = "Từ ngày: " + fromDate2.Value.ToString("dd/MM/yyyy") + " đến ngày: " + toDate2.Value.ToString("dd/MM/yyyy");

                    ////cau hình chung
                    //ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 1], exSheet.Cells[8 + dt.Rows.Count, HondaTypeList.Count]);
                    //rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    //rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    //rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    //rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[11, 1], exSheet.Cells[1000, 27]);
                    rng.Font.Size = 9;
                    rng.WrapText = true;
                    // đổ dữ liệu
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[11 + i, 1], exSheet.Cells[11 + i, 27]);
                        rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                        rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                        rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                        rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                        exSheet.Cells[11 + i, 1] = dt.Rows[i]["STT"].ToString();
                        exSheet.Cells[11 + i, 2] = dt.Rows[i]["SoPhieu"].ToString();
                        exSheet.Cells[11 + i, 3] = dt.Rows[i]["NgayDenSuaChua"].ToString();
                        exSheet.Cells[11 + i, 4] = dt.Rows[i]["LoaiXe"].ToString();
                        exSheet.Cells[11 + i, 5] = dt.Rows[i]["SoKM"].ToString();
                        exSheet.Cells[11 + i, 6] = "'" + dt.Rows[i]["SoKhung"].ToString();
                        exSheet.Cells[11 + i, 7] = "'" + dt.Rows[i]["BienSo"].ToString();

                        if (i != dt.Rows.Count - 1)
                        {
                            int luotrap = int.Parse(dt.Rows[i]["SLRapBan"].ToString());
                            int luotktdk = int.Parse(dt.Rows[i]["SLKTDK"].ToString());
                            int luotsuachua = int.Parse(dt.Rows[i]["SLSuaChua"].ToString());
                            if (luotrap >= 1)
                            {
                                exSheet.Cells[11 + i, 8] = "'1";
                                exSheet.Cells[11 + i, 9] = "'0";
                                exSheet.Cells[11 + i, 10] = "'0";
                            }
                            else if (luotktdk >= 1)
                            {
                                exSheet.Cells[11 + i, 8] = "'0";
                                exSheet.Cells[11 + i, 9] = "'1";
                                exSheet.Cells[11 + i, 10] = "'0";
                            }
                            else if (luotsuachua >= 1)
                            {
                                exSheet.Cells[11 + i, 8] = "'0";
                                exSheet.Cells[11 + i, 9] = "'0";
                                exSheet.Cells[11 + i, 10] = "'1";
                            }
                        }
                        else
                        {
                            exSheet.Cells[11 + i, 8] = "'" + dt.Rows[i]["SLRapBan"].ToString();
                            exSheet.Cells[11 + i, 9] = "'" + dt.Rows[i]["SLKTDK"].ToString();
                            exSheet.Cells[11 + i, 10] = "'" + dt.Rows[i]["SLSuaChua"].ToString();
                        }
                        exSheet.Cells[11 + i, 11] = dt.Rows[i]["TienCong"].ToString();
                        exSheet.Cells[11 + i, 12] = dt.Rows[i]["LaiGCN"].ToString();
                        exSheet.Cells[11 + i, 13] = dt.Rows[i]["LaiPTTM"].ToString();
                        exSheet.Cells[11 + i, 14] = dt.Rows[i]["TienPTThayThe"].ToString();
                        exSheet.Cells[11 + i, 15] = dt.Rows[i]["TienNhot"].ToString();
                        exSheet.Cells[11 + i, 16] = dt.Rows[i]["TongCong"].ToString();
                        exSheet.Cells[11 + i, 17] = dt.Rows[i]["HoTenKH"].ToString();
                        exSheet.Cells[11 + i, 18] = dt.Rows[i]["SoNha"].ToString();
                        exSheet.Cells[11 + i, 19] = dt.Rows[i]["TenDuong"].ToString();
                        exSheet.Cells[11 + i, 20] = dt.Rows[i]["Phuong"].ToString();
                        exSheet.Cells[11 + i, 21] = dt.Rows[i]["Quan"].ToString();
                        exSheet.Cells[11 + i, 22] = dt.Rows[i]["Tp"].ToString();
                        exSheet.Cells[11 + i, 23] = "'" + dt.Rows[i]["DienThoai"].ToString();
                        exSheet.Cells[11 + i, 24] = dt.Rows[i]["NVTiepNhan"].ToString();
                        exSheet.Cells[11 + i, 25] = "'" + dt.Rows[i]["MaNVTiepNhan"].ToString();
                        exSheet.Cells[11 + i, 26] = dt.Rows[i]["KTV"].ToString();
                        exSheet.Cells[11 + i, 27] = "'" + dt.Rows[i]["MaKTV"].ToString();
                    }
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[10 + dt.Rows.Count, 1], exSheet.Cells[10 + dt.Rows.Count, 27]);
                    rng.Font.Bold = true;
                    exBook.SaveAs(saveFileExcel.FileName, ComExcel.XlFileFormat.xlWorkbookNormal, null, null, false, false, ComExcel.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false);
                    
                    DialogResult dlg = MessageBox.Show("File báo cáo được lưu ở đường dẫn sau: " + saveFileExcel.FileName+"\r\n Bạn có muốn mở file không ?", "Thông Báo", MessageBoxButtons.YesNo);
                    if (dlg == DialogResult.Yes)
                    {
                        // hien thi chuong trinh excel
                        exApp.Visible = true;
                        exBook.ReadOnlyRecommended = false;
                    }
                    else
                    {
                        exBook.Close(false, false, false);
                        exApp.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
                    }
                }
            }
        }
        private void BC_DoanhSoDichVuHangKyChiTiet()
        {
            DataTable dt = PT_ServiceBillManager.CreateInstant().ReportDoanhSoDVHangKyChiTiet(fromDate2.Value, toDate2.Value);

            decimal Tong_Rap = 0;
            decimal Tong_KTDK = 0;
            decimal Tong_SuaChuaNgoai = 0;
            decimal Tong_Luot = 0;
            decimal Tong_ThoiGianRap = 0;
            decimal Tong_ThoiGianKTDK = 0;
            decimal Tong_ThoiGianSuaChua = 0;
            decimal Tong_ThoiGian = 0;
            decimal TongTienRap = 0;
            decimal TongTienKTDK = 0;
            decimal Tong_GCN_Thu = 0;
            decimal Tong_GCN_Chi = 0;
            decimal Tong_GCN_Lai = 0;
            decimal Tong_PTTM_Thu = 0;
            decimal Tong_PTTM_Chi = 0;
            decimal Tong_PTTM_Lai = 0;
            decimal Tong_DV_GiaBan = 0;
            decimal Tong_DV_GiamGia = 0;
            decimal Tong_DV_ThucThu = 0;
            decimal Tong_DV_BaoHanh = 0;
            decimal Tong_DV_ThienChi = 0;
            decimal Tong_DV_KhuyenMai = 0;
            decimal Tong_DSDV_CoThuTien = 0;
            decimal Tong_DSDV_KoThuTien = 0;
            decimal Tong_DoanhSoDV = 0;
            decimal Tong_SuaChua_GiaBan = 0;
            decimal Tong_SuaChua_GiamGia = 0;
            decimal Tong_SuaChua_ThucThu = 0;
            decimal Tong_PT_BaoHanh = 0;
            decimal Tong_PT_ThienChi = 0;
            decimal Tong_PT_KhuyenMai = 0;
            decimal Tong_GT_DS_KM = 0;
            decimal Tong_KM_SAU_GT = 0;
            decimal Tong_DoanhSo_PT_CoThuTien = 0;
            decimal Tong_DoanhSo_PT_KoThuTien = 0;
            decimal Tong_DoanhSoPT = 0;
            decimal Tong_5pcDoanhSoPT = 0;
            decimal Tong_DoanhSo_TinhThuong = 0;
            saveFileExcel.FileName = "BC2_DoanhSoDichVuHangKyChiTiet_"+DateTime.Now.ToString("dd_MM_yyyy_HH_mm")+".xls";
            saveFileExcel.DefaultExt = "xls";
            saveFileExcel.Filter = "Excel 2003 Files(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx|All Files|*.*";
            saveFileExcel.AddExtension = true;
            saveFileExcel.RestoreDirectory = true;
            saveFileExcel.Title = "Chọn đường dẫn lưu file";
            saveFileExcel.InitialDirectory = @"C:/";
            if (saveFileExcel.ShowDialog() == DialogResult.OK)
            {
                if (saveFileExcel.FileName.Split('\\')[saveFileExcel.FileName.Split('\\').Length - 1] == ".xls")
                {
                    MessageBox.Show("Nhập tên file cần lưu");
                }
                else
                {
                    // Khoi dong Excel
                    ComExcel.Application exApp = new ComExcel.Application();

                    //// them file temp xls moi
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);

                    // Mo mot file Excel co san
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);
                    ComExcel.Workbook exBook = exApp.Workbooks.Add(Application.StartupPath + "/ExcelTemplate/BC2.xls");

                    // Lay sheet 1
                    ComExcel.Worksheet exSheet = (ComExcel.Worksheet)exBook.Worksheets[1];
                    //exSheet.Activate();

                    // dia chi 
                    DataTable headdt = PT_ServiceBillManager.CreateInstant().GetReportHead(SessionLogin.UserId).Tables[0];
                    exSheet.Cells[3, 1] = headdt.Rows[0]["HEAD_ADD"];

                    //tu ngay- den ngay - dong 6 , cot 9
                    exSheet.Cells[6, 1] = "Từ ngày: " + fromDate2.Value.ToString("dd/MM/yyyy") + " đến ngày: " + toDate2.Value.ToString("dd/MM/yyyy");

                    ////cau hình chung
                    //ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 1], exSheet.Cells[8 + dt.Rows.Count, HondaTypeList.Count]);
                    //rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    //rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    //rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    //rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[11, 1], exSheet.Cells[1000, 41]);
                    rng.Font.Size = 9;
                    rng.WrapText = true;
                    // đổ dữ liệu
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[11 + i, 1], exSheet.Cells[11 + i, 41]);
                        rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                        rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                        rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                        rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;

                        exSheet.Cells[11 + i, 1] = (i + 1).ToString();
                        exSheet.Cells[11 + i, 2] = dt.Rows[i]["RepairedByName"].ToString();
                        exSheet.Cells[11 + i, 3] = "'" + dt.Rows[i]["RepairedByCode"].ToString();
                        decimal SoLuotRB = decimal.Parse(dt.Rows[i]["DV_SoLuotRB"].ToString());
                        exSheet.Cells[11 + i, 4] = SoLuotRB.ToString();
                        Tong_Rap += SoLuotRB;


                        decimal SoLuotKTDK = decimal.Parse(dt.Rows[i]["DV_SoLuotKTDK"].ToString());
                        exSheet.Cells[11 + i, 5] = SoLuotKTDK.ToString();
                        Tong_KTDK += SoLuotKTDK;

                        decimal SoLuotSuaChuaNgoaiKTDK = decimal.Parse(dt.Rows[i]["SoLuotSuaChuaNgoaiKTDK"].ToString());
                        exSheet.Cells[11 + i, 6] = SoLuotSuaChuaNgoaiKTDK.ToString();
                        Tong_SuaChuaNgoai += SoLuotSuaChuaNgoaiKTDK;

                        decimal SoLuotXe = decimal.Parse(dt.Rows[i]["DV_TongLuotXe"].ToString());
                        exSheet.Cells[11 + i, 7] = SoLuotXe.ToString();
                        Tong_Luot += SoLuotXe;

                        decimal ThoiGianRB = decimal.Parse(dt.Rows[i]["ThoiGianRB"].ToString());
                        exSheet.Cells[11 + i, 8] = ThoiGianRB.ToString();
                        Tong_ThoiGianRap += ThoiGianRB;

                        decimal ThoiGianKTDK = decimal.Parse(dt.Rows[i]["ThoiGianKTDK"].ToString());
                        exSheet.Cells[11 + i, 9] = ThoiGianKTDK.ToString();
                        Tong_ThoiGianKTDK += ThoiGianKTDK;

                        decimal DV_ThoiGianSua = decimal.Parse(dt.Rows[i]["DV_ThoiGianSua"].ToString());
                        exSheet.Cells[11 + i, 10] = DV_ThoiGianSua.ToString();
                        Tong_ThoiGianSuaChua += DV_ThoiGianSua;

                        decimal TongThoiGianSua = decimal.Parse(dt.Rows[i]["TongThoiGianSua"].ToString());
                        exSheet.Cells[11 + i, 11] = TongThoiGianSua.ToString();
                        Tong_ThoiGian += TongThoiGianSua;

                        decimal DV_DoanhSoRB = decimal.Parse(dt.Rows[i]["DV_DoanhSoRB"].ToString());
                        exSheet.Cells[11 + i, 12] = DV_DoanhSoRB.ToString();
                        TongTienRap += DV_DoanhSoRB;

                        decimal DV_DoanhSoKTDK = decimal.Parse(dt.Rows[i]["DV_DoanhSoKTDK"].ToString());
                        exSheet.Cells[11 + i, 13] = DV_DoanhSoKTDK.ToString();
                        TongTienKTDK += DV_DoanhSoKTDK;

                        decimal DV_ThuGCN = decimal.Parse(dt.Rows[i]["DV_ThuGCN"].ToString());
                        exSheet.Cells[11 + i, 14] = DV_ThuGCN.ToString();
                        Tong_GCN_Thu += DV_ThuGCN;

                        decimal DV_ChiGCN = decimal.Parse(dt.Rows[i]["DV_ChiGCN"].ToString());
                        exSheet.Cells[11 + i, 15] = DV_ChiGCN.ToString();
                        Tong_GCN_Chi += DV_ChiGCN;

                        decimal DV_LaiGCN = decimal.Parse(dt.Rows[i]["DV_LaiGCN"].ToString());
                        exSheet.Cells[11 + i, 16] = DV_LaiGCN.ToString();
                        Tong_GCN_Lai += DV_LaiGCN;

                        decimal DV_SC_GiaBan = decimal.Parse(dt.Rows[i]["DV_SC_GiaBan"].ToString());
                        exSheet.Cells[11 + i, 17] = DV_SC_GiaBan.ToString();
                        Tong_DV_GiaBan += DV_SC_GiaBan;

                        decimal DV_SC_GiamGia = decimal.Parse(dt.Rows[i]["DV_SC_GiamGia"].ToString());
                        exSheet.Cells[11 + i, 18] = DV_SC_GiamGia.ToString();
                        Tong_DV_GiamGia += DV_SC_GiamGia;

                        decimal DV_SC_ThucThu = decimal.Parse(dt.Rows[i]["DV_SC_ThucThu"].ToString());
                        exSheet.Cells[11 + i, 19] = DV_SC_ThucThu.ToString();
                        Tong_DV_ThucThu += DV_SC_ThucThu;
          
                        decimal DV_SuaChua_BaoHanh = decimal.Parse(dt.Rows[i]["DV_SuaChua_BaoHanh"].ToString());
                        exSheet.Cells[11 + i, 20] = DV_SuaChua_BaoHanh.ToString();
                        Tong_DV_BaoHanh += DV_SuaChua_BaoHanh;

                        decimal DV_SuaChua_ThienChi = decimal.Parse(dt.Rows[i]["DV_SuaChua_ThienChi"].ToString());
                        exSheet.Cells[11 + i, 21] = DV_SuaChua_ThienChi.ToString();
                        Tong_DV_ThienChi += DV_SuaChua_ThienChi;

                        decimal DV_SuaChua_KhuyenMai = decimal.Parse(dt.Rows[i]["DV_SuaChua_KhuyenMai"].ToString());
                        exSheet.Cells[11 + i, 22] = DV_SuaChua_KhuyenMai.ToString();
                        Tong_DV_KhuyenMai += DV_SuaChua_KhuyenMai;

                        decimal DS_DV_CoThuTien = decimal.Parse(dt.Rows[i]["DS_DV_CoThuTien"].ToString());
                        exSheet.Cells[11 + i, 23] = DS_DV_CoThuTien.ToString();
                        Tong_DSDV_CoThuTien += DS_DV_CoThuTien;

                        decimal DS_DV_KhongThuTien = decimal.Parse(dt.Rows[i]["DS_DV_KhongThuTien"].ToString());
                        exSheet.Cells[11 + i, 24] = DS_DV_KhongThuTien.ToString();
                        Tong_DSDV_KoThuTien += DS_DV_KhongThuTien;

                        decimal TongDSDV = decimal.Parse(dt.Rows[i]["TongDSDV"].ToString());
                        exSheet.Cells[11 + i, 25] = TongDSDV.ToString();
                        Tong_DoanhSoDV += TongDSDV;

                        decimal PT_SuaChua_GiaBan = decimal.Parse(dt.Rows[i]["PT_SuaChua_GiaBan"].ToString());
                        exSheet.Cells[11 + i, 26] = PT_SuaChua_GiaBan.ToString();
                        Tong_SuaChua_GiaBan += PT_SuaChua_GiaBan;

                        decimal PT_SuaChua_GiamGia = decimal.Parse(dt.Rows[i]["PT_SuaChua_GiamGia"].ToString());
                        exSheet.Cells[11 + i, 27] = PT_SuaChua_GiamGia.ToString();
                        Tong_SuaChua_GiamGia += PT_SuaChua_GiamGia;

                        decimal PT_SuaChua_ThucThu = decimal.Parse(dt.Rows[i]["PT_SuaChua_ThucThu"].ToString());
                        exSheet.Cells[11 + i, 28] = PT_SuaChua_ThucThu.ToString();
                        Tong_SuaChua_ThucThu += PT_SuaChua_ThucThu;

                        decimal PT_BaoHanh = decimal.Parse(dt.Rows[i]["PT_BaoHanh"].ToString());
                        exSheet.Cells[11 + i, 29] = PT_BaoHanh.ToString();
                        Tong_PT_BaoHanh += PT_BaoHanh;

                        decimal PT_ThienChi = decimal.Parse(dt.Rows[i]["PT_ThienChi"].ToString());
                        exSheet.Cells[11 + i, 30] = PT_ThienChi.ToString();
                        Tong_PT_ThienChi += PT_ThienChi;

                        decimal PT_KhuyenMai = decimal.Parse(dt.Rows[i]["PT_KhuyenMai"].ToString());
                        exSheet.Cells[11 + i, 31] = PT_KhuyenMai.ToString();
                        Tong_PT_KhuyenMai += PT_KhuyenMai;

                        decimal GT_DS_KM = decimal.Parse(dt.Rows[i]["PT_GTDSKM"].ToString());
                        exSheet.Cells[11 + i, 32] = GT_DS_KM.ToString();
                        Tong_GT_DS_KM += GT_DS_KM;

                        decimal KM_SAU_GT = decimal.Parse(dt.Rows[i]["PT_KMSGT"].ToString());
                        exSheet.Cells[11 + i, 33] = KM_SAU_GT.ToString();
                        Tong_KM_SAU_GT += KM_SAU_GT;

                        decimal PT_Thu_PTTM = decimal.Parse(dt.Rows[i]["PT_Thu_PTTM"].ToString());
                        exSheet.Cells[11 + i, 34] = PT_Thu_PTTM.ToString();
                        Tong_PTTM_Thu += PT_Thu_PTTM;

                        decimal PT_Chi_PTTM = decimal.Parse(dt.Rows[i]["PT_Chi_PTTM"].ToString());
                        exSheet.Cells[11 + i, 35] = PT_Chi_PTTM.ToString();
                        Tong_PTTM_Chi += PT_Chi_PTTM;

                        decimal PT_Lai_PTTM = decimal.Parse(dt.Rows[i]["PT_Lai_PTTM"].ToString());
                        exSheet.Cells[11 + i, 36] = PT_Lai_PTTM.ToString();
                        Tong_PTTM_Lai += PT_Lai_PTTM;

                        decimal DS_PT_Nhot_CoThuTien = decimal.Parse(dt.Rows[i]["DS_PT_Nhot_CoThuTien"].ToString());
                        exSheet.Cells[11 + i, 37] = DS_PT_Nhot_CoThuTien.ToString();
                        Tong_DoanhSo_PT_CoThuTien += DS_PT_Nhot_CoThuTien;

                        decimal DS_PT_Nhot_KhongThuTien = decimal.Parse(dt.Rows[i]["DS_PT_Nhot_KhongThuTien"].ToString());
                        exSheet.Cells[11 + i, 38] = DS_PT_Nhot_KhongThuTien.ToString();
                        Tong_DoanhSo_PT_KoThuTien += DS_PT_Nhot_KhongThuTien;

                        decimal TongDS_PT_Nhot = decimal.Parse(dt.Rows[i]["TongDS_PT_Nhot"].ToString());
                        exSheet.Cells[11 + i, 39] = TongDS_PT_Nhot.ToString();
                        Tong_DoanhSoPT += TongDS_PT_Nhot;

                        decimal PhanTram_DSPT_Nhot = decimal.Parse(dt.Rows[i]["PhanTram_DSPT_Nhot"].ToString());
                        exSheet.Cells[11 + i, 40] = PhanTram_DSPT_Nhot;
                        Tong_5pcDoanhSoPT += PhanTram_DSPT_Nhot;

                        decimal DoanhSoTinhThuong = decimal.Parse(dt.Rows[i]["DoanhSoTinhThuong"].ToString());
                        exSheet.Cells[11 + i, 41] = DoanhSoTinhThuong;
                        Tong_DoanhSo_TinhThuong += DoanhSoTinhThuong;
                    }
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[11 + dt.Rows.Count, 1], exSheet.Cells[11 + dt.Rows.Count, 41]);
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.Font.Bold = true;
                    exSheet.Cells[11 + dt.Rows.Count, 1] = "Tổng cộng";
                    exSheet.Cells[11 + dt.Rows.Count, 2] = "";
                    exSheet.Cells[11 + dt.Rows.Count, 3] = "";
                    exSheet.Cells[11 + dt.Rows.Count, 4] = Tong_Rap.ToString();
                    exSheet.Cells[11 + dt.Rows.Count, 5] = Tong_KTDK.ToString();
                    exSheet.Cells[11 + dt.Rows.Count, 6] = Tong_SuaChuaNgoai.ToString();
                    exSheet.Cells[11 + dt.Rows.Count, 7] = Tong_Luot.ToString();
                    exSheet.Cells[11 + dt.Rows.Count, 8] = Tong_ThoiGianRap.ToString();
                    exSheet.Cells[11 + dt.Rows.Count, 9] = Tong_ThoiGianKTDK.ToString();
                    exSheet.Cells[11 + dt.Rows.Count, 10] = Tong_ThoiGianSuaChua.ToString();
                    exSheet.Cells[11 + dt.Rows.Count, 11] = Tong_ThoiGian.ToString();
                    exSheet.Cells[11 + dt.Rows.Count, 12] = TongTienRap.ToString();
                    exSheet.Cells[11 + dt.Rows.Count, 13] = TongTienKTDK.ToString();
                    exSheet.Cells[11 + dt.Rows.Count, 14] = Tong_GCN_Thu.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 15] = Tong_GCN_Chi.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 16] = Tong_GCN_Lai.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 17] = Tong_DV_GiaBan.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 18] = Tong_DV_GiamGia.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 19] = Tong_DV_ThucThu.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 20] = Tong_DV_BaoHanh.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 21] = Tong_DV_ThienChi.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 22] = Tong_DV_KhuyenMai.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 23] = Tong_DSDV_CoThuTien.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 24] = Tong_DSDV_KoThuTien.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 25] = Tong_DoanhSoDV.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 26] = Tong_SuaChua_GiaBan.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 27] = Tong_SuaChua_GiamGia.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 28] = Tong_SuaChua_ThucThu.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 29] = Tong_PT_BaoHanh.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 30] = Tong_PT_ThienChi.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 31] = Tong_PT_KhuyenMai.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 32] = Tong_GT_DS_KM.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 33] = Tong_KM_SAU_GT.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 34] = Tong_PTTM_Thu.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 35] = Tong_PTTM_Chi.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 36] = Tong_PTTM_Lai.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 37] = Tong_DoanhSo_PT_CoThuTien.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 38] = Tong_DoanhSo_PT_KoThuTien.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 39] = Tong_DoanhSoPT.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 40] = Tong_5pcDoanhSoPT;

                    exSheet.Cells[11 + dt.Rows.Count, 41] = Tong_DoanhSo_TinhThuong;
                    
                    exBook.SaveAs(saveFileExcel.FileName, ComExcel.XlFileFormat.xlWorkbookNormal, null, null, false, false, ComExcel.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false);
                    DialogResult dlg = MessageBox.Show("File báo cáo được lưu ở đường dẫn sau: " + saveFileExcel.FileName + Environment.NewLine+ "Bạn có muốn mở file không ?", "Thông Báo", MessageBoxButtons.YesNo);
                    if (dlg == DialogResult.Yes)
                    {
                        // hien thi chuong trinh excel
                        exApp.Visible = true;
                        exBook.ReadOnlyRecommended = false;
                    }
                    else
                    {
                        exBook.Close(false, false, false);
                        exApp.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
                    }
                }
            }
        }
        private void BC_DoanhSoDichVuHangKy()
        {
            DataTable dt = PT_ServiceBillManager.CreateInstant().ReportDoanhSoDVHangKy(fromDate2.Value, toDate2.Value);

            decimal Tong_DV_SoLuotRB = 0;
            decimal Tong_DV_SoLuotKTDK = 0;
            decimal Tong_SoLuotSuaChuaNgoaiKTDK = 0;
            decimal Tong_DV_TongLuotXe = 0;
            decimal Tong_TongThoiGianSua = 0;
            decimal Tong_DV_DoanhSoRB = 0;
            decimal Tong_DV_DoanhSoKTDK = 0;
            decimal Tong_DV_TongDSDV = 0;
            decimal Tong_PT_DoanhSoPhuTung_Nhot = 0;
            decimal Tong_TongDoanhSo = 0;
            saveFileExcel.FileName = "BC3_DoanhSoDichVuHangKy_"+DateTime.Now.ToString("dd_MM_yyyy_HH_mm")+".xls";
            saveFileExcel.DefaultExt = "xls";
            saveFileExcel.Filter = "Excel 2003 Files(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx|All Files|*.*";
            saveFileExcel.AddExtension = true;
            saveFileExcel.RestoreDirectory = true;
            saveFileExcel.Title = "Chọn đường dẫn lưu file";
            saveFileExcel.InitialDirectory = @"C:/";
            if (saveFileExcel.ShowDialog() == DialogResult.OK)
            {
                if (saveFileExcel.FileName.Split('\\')[saveFileExcel.FileName.Split('\\').Length - 1] == ".xls")
                {
                    MessageBox.Show("Nhập tên file cần lưu");
                }
                else
                {
                    // Khoi dong Excel
                    ComExcel.Application exApp = new ComExcel.Application();

                    //// them file temp xls moi
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);

                    // Mo mot file Excel co san
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);
                    ComExcel.Workbook exBook = exApp.Workbooks.Add(Application.StartupPath + "/ExcelTemplate/BC3.xls");

                    // Lay sheet 1
                    ComExcel.Worksheet exSheet = (ComExcel.Worksheet)exBook.Worksheets[1];
                    //exSheet.Activate();

                    // dia chi 
                    DataTable headdt = PT_ServiceBillManager.CreateInstant().GetReportHead(SessionLogin.UserId).Tables[0];
                    exSheet.Cells[3, 1] = headdt.Rows[0]["HEAD_ADD"];

                    //tu ngay- den ngay - dong 6 , cot 9
                    exSheet.Cells[6, 1] = "Từ ngày: " + fromDate2.Value.ToString("dd/MM/yyyy") + " đến ngày: " + toDate2.Value.ToString("dd/MM/yyyy");

                    ////cau hình chung
                    //ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 1], exSheet.Cells[8 + dt.Rows.Count, HondaTypeList.Count]);
                    //rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    //rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    //rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    //rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[10, 1], exSheet.Cells[1000, 15]);
                    rng.Font.Size = 9;
                    rng.WrapText = true;
                    // đổ dữ liệu
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[10 + i, 1], exSheet.Cells[10 + i, 15]);
                        rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                        rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                        rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                        rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;

                        exSheet.Cells[10 + i, 1] = (i + 1).ToString();
                        exSheet.Cells[10 + i, 2] = dt.Rows[i]["RepairedByName"].ToString();
                        exSheet.Cells[10 + i, 3] = "'" + dt.Rows[i]["RepairedByCode"].ToString();

                        decimal DV_SoLuotRB = decimal.Parse(dt.Rows[i]["DV_SoLuotRB"].ToString());
                        exSheet.Cells[10 + i, 4] = DV_SoLuotRB.ToString();
                        Tong_DV_SoLuotRB += DV_SoLuotRB;

                        decimal DV_SoLuotKTDK = decimal.Parse(dt.Rows[i]["DV_SoLuotKTDK"].ToString());
                        exSheet.Cells[10 + i, 5] = DV_SoLuotKTDK.ToString();
                        Tong_DV_SoLuotKTDK += DV_SoLuotKTDK;

                        decimal SoLuotSuaChuaNgoaiKTDK = decimal.Parse(dt.Rows[i]["SoLuotSuaChuaNgoaiKTDK"].ToString());
                        exSheet.Cells[10 + i, 6] = SoLuotSuaChuaNgoaiKTDK.ToString();
                        Tong_SoLuotSuaChuaNgoaiKTDK += SoLuotSuaChuaNgoaiKTDK;

                        decimal DV_TongLuotXe = decimal.Parse(dt.Rows[i]["DV_TongLuotXe"].ToString());
                        exSheet.Cells[10 + i, 7] = DV_TongLuotXe.ToString();
                        Tong_DV_TongLuotXe += DV_TongLuotXe;

                        decimal TongThoiGianSua = decimal.Parse(dt.Rows[i]["TongThoiGianSua"].ToString());
                        exSheet.Cells[10 + i, 8] = TongThoiGianSua.ToString();
                        Tong_TongThoiGianSua += TongThoiGianSua;

                        decimal DV_DoanhSoRB = decimal.Parse(dt.Rows[i]["DV_DoanhSoRB"].ToString());
                        exSheet.Cells[10 + i, 9] = DV_DoanhSoRB.ToString();
                        Tong_DV_DoanhSoRB += DV_DoanhSoRB;

                        decimal DV_DoanhSoKTDK = decimal.Parse(dt.Rows[i]["DV_DoanhSoKTDK"].ToString());
                        exSheet.Cells[10 + i, 10] = DV_DoanhSoKTDK.ToString();
                        Tong_DV_DoanhSoKTDK += DV_DoanhSoKTDK;

                        decimal DV_TongDSDV = decimal.Parse(dt.Rows[i]["DV_TongDSDV"].ToString());
                        exSheet.Cells[10 + i, 11] = DV_TongDSDV.ToString();
                        Tong_DV_TongDSDV += DV_TongDSDV;

                        decimal PT_DoanhSoPhuTung_Nhot = decimal.Parse(dt.Rows[i]["PT_DoanhSoPhuTung_Nhot"].ToString());
                        exSheet.Cells[10 + i, 12] = PT_DoanhSoPhuTung_Nhot.ToString();
                        Tong_PT_DoanhSoPhuTung_Nhot += PT_DoanhSoPhuTung_Nhot;

                        decimal TongDoanhSo = decimal.Parse(dt.Rows[i]["TongDoanhSo"].ToString());
                        exSheet.Cells[10 + i, 13] = TongDoanhSo.ToString();
                        Tong_TongDoanhSo += TongDoanhSo;
                    }
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[10 + dt.Rows.Count, 1], exSheet.Cells[10 + dt.Rows.Count, 15]);
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.Font.Bold = true;
                    exSheet.Cells[10 + dt.Rows.Count, 1] = "Tổng cộng";
                    exSheet.Cells[10 + dt.Rows.Count, 2] = "";
                    exSheet.Cells[10 + dt.Rows.Count, 3] = "";
                    exSheet.Cells[10 + dt.Rows.Count, 4] = Tong_DV_SoLuotRB.ToString();

                    exSheet.Cells[10 + dt.Rows.Count, 5] = Tong_DV_SoLuotKTDK.ToString();

                    exSheet.Cells[10 + dt.Rows.Count, 6] = Tong_SoLuotSuaChuaNgoaiKTDK.ToString();

                    exSheet.Cells[10 + dt.Rows.Count, 7] = Tong_DV_TongLuotXe.ToString();

                    exSheet.Cells[10 + dt.Rows.Count, 8] = Tong_TongThoiGianSua.ToString();

                    exSheet.Cells[10 + dt.Rows.Count, 9] = Tong_DV_DoanhSoRB.ToString();

                    exSheet.Cells[10 + dt.Rows.Count, 10] = Tong_DV_DoanhSoKTDK.ToString();

                    exSheet.Cells[10 + dt.Rows.Count, 11] = Tong_DV_TongDSDV.ToString();

                    exSheet.Cells[10 + dt.Rows.Count, 12] = Tong_PT_DoanhSoPhuTung_Nhot.ToString();

                    exSheet.Cells[10 + dt.Rows.Count, 13] = Tong_TongDoanhSo.ToString();

                    exBook.SaveAs(saveFileExcel.FileName, ComExcel.XlFileFormat.xlWorkbookNormal, null, null, false, false, ComExcel.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false);
                    DialogResult dlg = MessageBox.Show("File báo cáo được lưu ở đường dẫn sau: " + saveFileExcel.FileName + Environment.NewLine + "Bạn có muốn mở file không ?", "Thông Báo", MessageBoxButtons.YesNo);
                    if (dlg == DialogResult.Yes)
                    {
                        // hien thi chuong trinh excel
                        exApp.Visible = true;
                        exBook.ReadOnlyRecommended = false;
                    }
                    else
                    {
                        exBook.Close(false, false, false);
                        exApp.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
                    }
                }
            }
        }
        private void BC_TongHopSoLuongXeDenCuaHang()
        {
            DateTime fromDate = fromDate2.Value;
            DateTime toDate = toDate2.Value;
            DataTable dt = PT_ServiceBillManager.CreateInstant().Report(fromDate, toDate);

            DataTable cusdt = new DataTable();
            cusdt.Columns.Add("STT");
            cusdt.Columns.Add("Ngày CT");
            PT_HondaModelGroupEntity GroupOption = new PT_HondaModelGroupEntity();
            GroupOption.IsDeleted = false;
            GroupOption.IsActive = true;
            SelectOptions op = new SelectOptions();
            op.AddSortField("OrderIndex", true);
            List<PT_HondaModelGroupEntity> HondaTypeList = PT_HondaModelGroupManager.CreateInstant().SelectWithConditionLST(GroupOption, op);
            for (int i = 0; i < HondaTypeList.Count; i++)
            {
                cusdt.Columns.Add(HondaTypeList[i].GroupName);
            }
            cusdt.Columns.Add("TỔNG");
            cusdt.Columns.Add("SỐ LẦN BẢO DƯỠNG ĐỊNH KỲ");
            cusdt.Columns.Add("DCSC NHỎ (KHÔNG THAY PHỤ TÙNG)");
            cusdt.Columns.Add("DCSC NHỎ (CÓ THAY PHỤ TÙNG)");
            cusdt.Columns.Add("DCSC LỚN");
            cusdt.Columns.Add("NHỚT");
            DataTable dt2 = PT_ServiceBillManager.CreateInstant().ReportPart2(fromDate, toDate);

            decimal t = 0;
            decimal tongBDDK = 0;
            decimal tongNhoko = 0;
            decimal tongNhoCo = 0;
            decimal tongLon = 0;
            decimal tongNhot = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = cusdt.NewRow();
                dr["STT"] = (i + 1).ToString();
                dr["Ngày CT"] = dt.Rows[i]["InputDate"].ToString();
                decimal tong = 0;
                for (int j = 0; j < HondaTypeList.Count; j++)
                {
                    dr[HondaTypeList[j].GroupName] = dt.Rows[i][HondaTypeList[j].GroupName].ToString();
                    tong += !string.IsNullOrEmpty(dt.Rows[i][HondaTypeList[j].GroupName].ToString()) ? decimal.Parse(dt.Rows[i][HondaTypeList[j].GroupName].ToString()) : 0;
                }
                dr["TỔNG"] = tong.ToString();
                dr["SỐ LẦN BẢO DƯỠNG ĐỊNH KỲ"] = dt2.Rows[i]["BDDK"].ToString();
                dr["DCSC NHỎ (KHÔNG THAY PHỤ TÙNG)"] = dt2.Rows[i]["SuaChuaNhoHon1hkoPhuTung"].ToString();
                dr["DCSC NHỎ (CÓ THAY PHỤ TÙNG)"] = dt2.Rows[i]["SuaChuaNhoHon1hCoPhuTung"].ToString();
                dr["DCSC LỚN"] = dt2.Rows[i]["SuaChuaLonHon1h"].ToString();
                dr["NHỚT"] = dt2.Rows[i]["Nhot"].ToString();
                cusdt.Rows.Add(dr);
                t += tong;
                tongBDDK += !string.IsNullOrEmpty(dt2.Rows[i]["BDDK"].ToString()) ? decimal.Parse(dt2.Rows[i]["BDDK"].ToString()) : 0;
                tongNhoko += !string.IsNullOrEmpty(dt2.Rows[i]["SuaChuaNhoHon1hkoPhuTung"].ToString()) ? decimal.Parse(dt2.Rows[i]["SuaChuaNhoHon1hkoPhuTung"].ToString()) : 0;
                tongNhoCo += !string.IsNullOrEmpty(dt2.Rows[i]["SuaChuaNhoHon1hCoPhuTung"].ToString()) ? decimal.Parse(dt2.Rows[i]["SuaChuaNhoHon1hCoPhuTung"].ToString()) : 0;
                tongLon += !string.IsNullOrEmpty(dt2.Rows[i]["SuaChuaLonHon1h"].ToString()) ? decimal.Parse(dt2.Rows[i]["SuaChuaLonHon1h"].ToString()) : 0;
                tongNhot += !string.IsNullOrEmpty(dt2.Rows[i]["Nhot"].ToString()) ? decimal.Parse(dt2.Rows[i]["Nhot"].ToString()) : 0;
            }
            DataRow dr1 = cusdt.NewRow();
            dr1["STT"] = "TỔNG";
            dr1["TỔNG"] = t.ToString();
            dr1["SỐ LẦN BẢO DƯỠNG ĐỊNH KỲ"] = tongBDDK.ToString();
            dr1["DCSC NHỎ (KHÔNG THAY PHỤ TÙNG)"] = tongNhoko.ToString();
            dr1["DCSC NHỎ (CÓ THAY PHỤ TÙNG)"] = tongNhoCo.ToString();
            dr1["DCSC LỚN"] = tongLon.ToString();
            dr1["NHỚT"] = tongNhot.ToString();
            for (int j = 0; j < HondaTypeList.Count; j++)
            {
                decimal t2 = 0;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    t2 += !string.IsNullOrEmpty(dt.Rows[i][HondaTypeList[j].GroupName].ToString()) ? decimal.Parse(dt.Rows[i][HondaTypeList[j].GroupName].ToString()) : 0;
                }
                dr1[HondaTypeList[j].GroupName] = t2.ToString();
            }
            cusdt.Rows.Add(dr1);

            saveFileExcel.FileName = "BC4_TongHopSoLuongXeDenCuaHang_"+DateTime.Now.ToString("dd_MM_yyyy_HH_mm")+".xls";
            saveFileExcel.DefaultExt = "xls";
            saveFileExcel.Filter = "Excel 2003 Files(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx|All Files|*.*";
            saveFileExcel.AddExtension = true;
            saveFileExcel.RestoreDirectory = true;
            saveFileExcel.Title = "Chọn đường dẫn lưu file";
            saveFileExcel.InitialDirectory = @"C:/";
            if (saveFileExcel.ShowDialog() == DialogResult.OK)
            {
                if (saveFileExcel.FileName.Split('\\')[saveFileExcel.FileName.Split('\\').Length - 1] == ".xls")
                {
                    MessageBox.Show("Nhập tên file cần lưu");
                }
                else
                {
                    // Khoi dong Excel
                    ComExcel.Application exApp = new ComExcel.Application();

                    //// them file temp xls moi
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);

                    // Mo mot file Excel co san
                    ComExcel.Workbook exBook = exApp.Workbooks.Add(Application.StartupPath + "/ExcelTemplate/BC4.xls");

                    // Lay sheet 1
                    ComExcel.Worksheet exSheet = (ComExcel.Worksheet)exBook.Worksheets[1];
                    //exSheet.Activate();

                    // dia chi 
                    DataTable headdt = PT_ServiceBillManager.CreateInstant().GetReportHead(SessionLogin.UserId).Tables[0];
                    exSheet.Cells[3, 1] = headdt.Rows[0]["HEAD_ADD"];

                    //tu ngay- den ngay - dong 6 , cot 9
                    exSheet.Cells[6, 7] = "Từ ngày: " + fromDate2.Value.ToString("dd/MM/yyyy") + " đến ngày: " + toDate2.Value.ToString("dd/MM/yyyy");

                    ////cau hình chung
                    //ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 1], exSheet.Cells[8 + dt.Rows.Count, HondaTypeList.Count]);
                    //rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    //rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    //rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    //rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 1], exSheet.Cells[1000, 100]);
                    rng.Font.Size = 9;
                    //STT
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 1], exSheet.Cells[dt.Rows.Count > 0 ? 11 + dt.Rows.Count : 11, HondaTypeList.Count + 14]);
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;

                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 1], exSheet.Cells[10, 1]);
                    exSheet.Cells[8, 1] = "STT";
                    rng.MergeCells = true;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.WrapText = true;
                    rng.ColumnWidth = 7;
                    rng.RowHeight = 50;
                    //Ngày CT
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 2], exSheet.Cells[10, 2]);
                    exSheet.Cells[8, 2] = "NGÀY CT";
                    rng.MergeCells = true;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.WrapText = true;
                    rng.ColumnWidth = 15;
                    rng.RowHeight = 50;

                    // Phan loai xe
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 3], exSheet.Cells[8, 2 + HondaTypeList.Count]);
                    rng.MergeCells = true;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.RowHeight = 20;
                    exSheet.Cells[8, 3] = "PHÂN LOẠI XE";
                    // chi tiet phan loai xe
                    for (int i = 0; i < HondaTypeList.Count; i++)
                    {
                        rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[9, 3 + i], exSheet.Cells[10, 3 + i]);
                        rng.MergeCells = true;
                        rng.ColumnWidth = 7;
                        rng.WrapText = true;
                        rng.WrapText = true;
                        rng.RowHeight = 100;
                        rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                        rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                        exSheet.Cells[9, 3 + i] = HondaTypeList[i].GroupName;
                    }

                    //Tong
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 3 + HondaTypeList.Count], exSheet.Cells[10, 3 + HondaTypeList.Count]);
                    rng.MergeCells = true;
                    rng.ColumnWidth = 7;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.RowHeight = 80;
                    exSheet.Cells[8, 3 + HondaTypeList.Count] = "TỔNG";

                    // Phân loại dịch vụ
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 4 + HondaTypeList.Count], exSheet.Cells[8, 12 + HondaTypeList.Count]);
                    rng.MergeCells = true;
                    rng.WrapText = true;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.RowHeight = 20;
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    exSheet.Cells[8, 4 + HondaTypeList.Count] = "PHÂN LOẠI DỊCH VỤ";

                    // Chi tiết phân loại dịch vụ                    
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[9, 4 + HondaTypeList.Count], exSheet.Cells[10, 4 + HondaTypeList.Count]);
                    rng.MergeCells = true;
                    rng.WrapText = true;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.ColumnWidth = 8;
                    rng.WrapText = true;
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    exSheet.Cells[9, 4 + HondaTypeList.Count] = "TỔNG LƯỢT XE BDDK TẠI CỬA HÀNG";

                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[9, 5 + HondaTypeList.Count], exSheet.Cells[9, 6 + HondaTypeList.Count]);
                    rng.MergeCells = true;
                    rng.WrapText = true;
                    rng.ColumnWidth = 16;
                    rng.RowHeight = 100;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    exSheet.Cells[9, 5 + HondaTypeList.Count] = "KIỂM TRA ĐỊNH KỲ THEO HƯỚNG DẪN CỦA HVN";

                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[9, 7 + HondaTypeList.Count], exSheet.Cells[9, 8 + HondaTypeList.Count]);
                    rng.MergeCells = true;
                    rng.WrapText = true;
                    rng.ColumnWidth = 15;
                    rng.RowHeight = 100;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    exSheet.Cells[9, 7 + HondaTypeList.Count] = "KIỂM TRA ĐỊNH KỲ CHO XE MUA TỪ CỬA HÀNG KHÁC";

                    rng = (ComExcel.Range)exSheet.Cells[10, 5 + HondaTypeList.Count];
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.ColumnWidth = 7;
                    rng.RowHeight = 40;
                    rng.WrapText = true;
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    exSheet.Cells[10, 5 + HondaTypeList.Count] = "4 LẦN";

                    rng = (ComExcel.Range)exSheet.Cells[10, 6 + HondaTypeList.Count];
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.ColumnWidth = 7;
                    rng.RowHeight = 40;
                    rng.WrapText = true;
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    exSheet.Cells[10, 6 + HondaTypeList.Count] = "6 LẦN";

                    rng = (ComExcel.Range)exSheet.Cells[10, 7 + HondaTypeList.Count];
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.ColumnWidth = 7;
                    rng.WrapText = true;
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    exSheet.Cells[10, 7 + HondaTypeList.Count] = "4 LẦN";

                    rng = (ComExcel.Range)exSheet.Cells[10, 8 + HondaTypeList.Count];
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.ColumnWidth = 7;
                    rng.WrapText = true;
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    exSheet.Cells[10, 8 + HondaTypeList.Count] = "6 LẦN";

                    // điều chỉnh, sửa chữa nhỏ (Không thay phụ tùng)
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[9, 9 + HondaTypeList.Count], exSheet.Cells[10, 9 + HondaTypeList.Count]);
                    rng.MergeCells = true;
                    rng.WrapText = true;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.ColumnWidth = 10;
                    rng.WrapText = true;
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    exSheet.Cells[9, 9 + HondaTypeList.Count] = "ĐIỀU CHỈNH, SỬA CHỮA NHỎ (KHÔNG THAY PHỤ TÙNG)";

                    //// sửa chữa có thay phụ tùng
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[9, 10 + HondaTypeList.Count], exSheet.Cells[10, 10 + HondaTypeList.Count]);
                    rng.MergeCells = true;
                    rng.WrapText = true;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.ColumnWidth = 10;
                    rng.WrapText = true;
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    exSheet.Cells[9, 10 + HondaTypeList.Count] = "SỬA CHỮA CÓ THAY PHỤ TÙNG (FRT<1 GIỜ)";

                    //// sửa chữa lớn
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[9, 11 + HondaTypeList.Count], exSheet.Cells[10, 11 + HondaTypeList.Count]);
                    rng.MergeCells = true;
                    rng.WrapText = true;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.ColumnWidth = 10;
                    rng.WrapText = true;
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    exSheet.Cells[9, 11 + HondaTypeList.Count] = "SỬA CHỮA LỚN (FRT>=1 GIỜ)";


                    //// Thay dầu nhớt
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[9, 12 + HondaTypeList.Count], exSheet.Cells[10, 12 + HondaTypeList.Count]);
                    rng.MergeCells = true;
                    rng.WrapText = true;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.ColumnWidth = 10;
                    rng.WrapText = true;
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    exSheet.Cells[9, 12 + HondaTypeList.Count] = "THAY DẦU NHỚT";

                    //// rửa xe
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[9, 13 + HondaTypeList.Count], exSheet.Cells[10, 13 + HondaTypeList.Count]);
                    rng.MergeCells = true;
                    rng.WrapText = true;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.ColumnWidth = 10;
                    rng.WrapText = true;
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    exSheet.Cells[9, 13 + HondaTypeList.Count] = "RỬA XE";

                    //// Tổng - Phân loại dịch vụ
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 14 + HondaTypeList.Count], exSheet.Cells[10, 14 + HondaTypeList.Count]);
                    rng.MergeCells = true;
                    rng.WrapText = true;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.ColumnWidth = 10;
                    rng.MergeCells = true;
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    exSheet.Cells[8, 14 + HondaTypeList.Count] = "TỔNG";

                    // đổ dữ liệu
                    for (int i = 0; i < cusdt.Rows.Count; i++)
                    {
                        rng = (ComExcel.Range)exSheet.Cells[11 + i, 1];
                        rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                        exSheet.Cells[11 + i, 1] = cusdt.Rows[i]["STT"].ToString();

                        rng = (ComExcel.Range)exSheet.Cells[11 + i, 2];
                        rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                        rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                        exSheet.Cells[11 + i, 2] = cusdt.Rows[i]["NGÀY CT"].ToString();

                        for (int j = 0; j < HondaTypeList.Count; j++)
                        {
                            rng = (ComExcel.Range)exSheet.Cells[11 + i, 3 + j];
                            rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                            exSheet.Cells[11 + i, 3 + j] = !string.IsNullOrEmpty(cusdt.Rows[i][HondaTypeList[j].GroupName].ToString()) ? cusdt.Rows[i][HondaTypeList[j].GroupName].ToString() : "0";
                        }
                        rng = (ComExcel.Range)exSheet.Cells[11 + i, HondaTypeList.Count + 3];
                        rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                        exSheet.Cells[11 + i, HondaTypeList.Count + 3] = cusdt.Rows[i]["TỔNG"].ToString();

                        rng = (ComExcel.Range)exSheet.Cells[11 + i, HondaTypeList.Count + 4];
                        rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                        exSheet.Cells[11 + i, HondaTypeList.Count + 4] = cusdt.Rows[i]["SỐ LẦN BẢO DƯỠNG ĐỊNH KỲ"].ToString();

                        rng = (ComExcel.Range)exSheet.Cells[11 + i, HondaTypeList.Count + 9];
                        rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                        exSheet.Cells[11 + i, HondaTypeList.Count + 9] = cusdt.Rows[i]["DCSC NHỎ (KHÔNG THAY PHỤ TÙNG)"].ToString();

                        rng = (ComExcel.Range)exSheet.Cells[11 + i, HondaTypeList.Count + 10];
                        rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                        exSheet.Cells[11 + i, HondaTypeList.Count + 10] = cusdt.Rows[i]["DCSC NHỎ (CÓ THAY PHỤ TÙNG)"].ToString();

                        rng = (ComExcel.Range)exSheet.Cells[11 + i, HondaTypeList.Count + 11];
                        rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                        exSheet.Cells[11 + i, HondaTypeList.Count + 11] = cusdt.Rows[i]["DCSC LỚN"].ToString();

                        rng = (ComExcel.Range)exSheet.Cells[11 + i, HondaTypeList.Count + 12];
                        rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                        exSheet.Cells[11 + i, HondaTypeList.Count + 12] = cusdt.Rows[i]["NHỚT"].ToString();
                    }
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[11 + dt.Rows.Count, 1], exSheet.Cells[11 + dt.Rows.Count, 12 + HondaTypeList.Count]);
                    rng.Font.Bold = true;

                    exBook.SaveAs(saveFileExcel.FileName, ComExcel.XlFileFormat.xlWorkbookNormal, null, null, false, false, ComExcel.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false);
                    DialogResult dlg = MessageBox.Show("File báo cáo được lưu ở đường dẫn sau: " + saveFileExcel.FileName + Environment.NewLine + "Bạn có muốn mở file không ?", "Thông Báo", MessageBoxButtons.YesNo);
                    if (dlg == DialogResult.Yes)
                    {
                        // hien thi chuong trinh excel
                        exApp.Visible = true;
                        exBook.ReadOnlyRecommended = false;
                    }
                    else
                    {
                        exBook.Close(false, false, false);
                        exApp.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
                    }
                }
            }
        }
        private void BC_TongHopSoLanDenCuaKhachHang()
        {
            DataTable dt = PT_ServiceBillManager.CreateInstant().ReportTongHopLanDenCuaKhachHang(fromDate2.Value, toDate2.Value);
            saveFileExcel.FileName = "BC5_TongHopSoLanDenCuaKhachHang_"+DateTime.Now.ToString("dd_MM_yyyy_HH_mm")+".xls";
            saveFileExcel.DefaultExt = "xls";
            saveFileExcel.Filter = "Excel 2003 Files(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx|All Files|*.*";
            saveFileExcel.AddExtension = true;
            saveFileExcel.RestoreDirectory = true;
            saveFileExcel.Title = "Chọn đường dẫn lưu file";
            saveFileExcel.InitialDirectory = @"C:/";
            if (saveFileExcel.ShowDialog() == DialogResult.OK)
            {
                if (saveFileExcel.FileName.Split('\\')[saveFileExcel.FileName.Split('\\').Length - 1] == ".xls")
                {
                    MessageBox.Show("Nhập tên file cần lưu");
                }
                else
                {
                    // Khoi dong Excel
                    ComExcel.Application exApp = new ComExcel.Application();

                    //// them file temp xls moi
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);

                    // Mo mot file Excel co san
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);
                    ComExcel.Workbook exBook = exApp.Workbooks.Add(Application.StartupPath + "/ExcelTemplate/BC5.xls");

                    // Lay sheet 1
                    ComExcel.Worksheet exSheet = (ComExcel.Worksheet)exBook.Worksheets[1];
                    //exSheet.Activate();

                    // dia chi 
                    DataTable headdt = PT_ServiceBillManager.CreateInstant().GetReportHead(SessionLogin.UserId).Tables[0];
                    exSheet.Cells[3, 1] = headdt.Rows[0]["HEAD_ADD"];

                    //tu ngay- den ngay - dong 6 , cot 9
                    exSheet.Cells[5, 1] = "Từ ngày: " + fromDate2.Value.ToString("dd/MM/yyyy") + " đến ngày: " + toDate2.Value.ToString("dd/MM/yyyy");

                    ////cau hình chung
                    //ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 1], exSheet.Cells[8 + dt.Rows.Count, HondaTypeList.Count]);
                    //rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    //rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    //rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    //rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 1], exSheet.Cells[dt.Rows.Count + 20, 17]);
                    rng.Font.Size = 9;
                    rng.WrapText = true;
                    // đổ dữ liệu
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8 + i, 1], exSheet.Cells[8 + i, 17]);
                        rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                        rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;

                        exSheet.Cells[8 + i, 1] = (i + 1).ToString();
                        exSheet.Cells[8 + i, 2] = AF.Library.FDateTime.ConvertDate(dt.Rows[i]["Ngay"].ToString()).ToString("dd/MM/yyyy");
                        exSheet.Cells[8 + i, 3] = dt.Rows[i]["CustName"].ToString();
                        exSheet.Cells[8 + i, 4] = dt.Rows[i]["SoLuotRB"].ToString();
                        exSheet.Cells[8 + i, 5] = dt.Rows[i]["SoLuotKTDK"].ToString();
                        exSheet.Cells[8 + i, 6] = dt.Rows[i]["SoLuotSC"].ToString();
                        exSheet.Cells[8 + i, 7] = dt.Rows[i]["TongLuot"].ToString();
                        exSheet.Cells[8 + i, 8] = "'" + dt.Rows[i]["NumberPlate"].ToString();
                        exSheet.Cells[8 + i, 9] = dt.Rows[i]["Model"].ToString();
                        exSheet.Cells[8 + i, 10] = dt.Rows[i]["TotalMoney"].ToString();
                        exSheet.Cells[8 + i, 11] = dt.Rows[i]["TienPhatSinhTrungBinh"].ToString();
                        exSheet.Cells[8 + i, 12] = "'" + dt.Rows[i]["Phone"].ToString();
                        exSheet.Cells[8 + i, 13] = "'" + dt.Rows[i]["AddressNumber"].ToString();
                        exSheet.Cells[8 + i, 14] = "'" + dt.Rows[i]["Address"].ToString();
                        exSheet.Cells[8 + i, 15] = "'" + dt.Rows[i]["Ward"].ToString();
                        exSheet.Cells[8 + i, 16] = "'" + dt.Rows[i]["DistrictName"].ToString();
                        exSheet.Cells[8 + i, 17] = "'" + dt.Rows[i]["ProvinceName"].ToString();
                    }
                  
                    exBook.SaveAs(saveFileExcel.FileName, ComExcel.XlFileFormat.xlWorkbookNormal, null, null, false, false, ComExcel.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false);
                    DialogResult dlg = MessageBox.Show("File báo cáo được lưu ở đường dẫn sau: " + saveFileExcel.FileName + Environment.NewLine + "Bạn có muốn mở file không ?", "Thông Báo", MessageBoxButtons.YesNo);
                    if (dlg == DialogResult.Yes)
                    {
                        // hien thi chuong trinh excel
                        exApp.Visible = true;
                        exBook.ReadOnlyRecommended = false;
                    }
                    else
                    {
                        exBook.Close(false, false, false);
                        exApp.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
                    }
                }
            }
        }
        private void BC_DoanhThuDichVuPhuTungNhot()
        {
            DataTable dt = PT_ServiceBillManager.CreateInstant().ReportDoanhThuDichVuPhuTungNhot(fromDate2.Value, toDate2.Value);
            decimal Tong_TienCongTuKH = 0;
            decimal Tong_PT_MangVe = 0;
            decimal Tong_Nhot_MangVe = 0;
            decimal Tong_PT_TaiCuaHang = 0;
            decimal Tong_Nhot_TaiCuaHang = 0;
            decimal Tong_Tong = 0;
            saveFileExcel.FileName = "BC6_DoanhSoDichVuPhuTungNhot_" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm") + ".xls";
            saveFileExcel.DefaultExt = "xls";
            saveFileExcel.Filter = "Excel 2003 Files(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx|All Files|*.*";
            saveFileExcel.AddExtension = true;
            saveFileExcel.RestoreDirectory = true;
            saveFileExcel.Title = "Chọn đường dẫn lưu file";
            saveFileExcel.InitialDirectory = @"C:/";
            if (saveFileExcel.ShowDialog() == DialogResult.OK)
            {
                if (saveFileExcel.FileName.Split('\\')[saveFileExcel.FileName.Split('\\').Length - 1] == ".xls")
                {
                    MessageBox.Show("Nhập tên file cần lưu");
                }
                else
                {
                    // Khoi dong Excel
                    ComExcel.Application exApp = new ComExcel.Application();

                    //// them file temp xls moi
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);

                    // Mo mot file Excel co san
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);
                    ComExcel.Workbook exBook = exApp.Workbooks.Add(Application.StartupPath + "/ExcelTemplate/BC6.xls");

                    // Lay sheet 1
                    ComExcel.Worksheet exSheet = (ComExcel.Worksheet)exBook.Worksheets[1];
                    //exSheet.Activate();

                    // dia chi 
                    DataTable headdt = PT_ServiceBillManager.CreateInstant().GetReportHead(SessionLogin.UserId).Tables[0];
                    exSheet.Cells[3, 1] = headdt.Rows[0]["HEAD_ADD"];

                    //tu ngay- den ngay - dong 6 , cot 9
                    exSheet.Cells[6, 1] = "Từ ngày: " + fromDate2.Value.ToString("dd/MM/yyyy") + " đến ngày: " + toDate2.Value.ToString("dd/MM/yyyy");

                    ////cau hình chung
                    //ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 1], exSheet.Cells[8 + dt.Rows.Count, HondaTypeList.Count]);
                    //rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    //rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    //rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    //rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[11, 1], exSheet.Cells[1000, 9]);
                    rng.Font.Size = 9;
                    rng.WrapText = true;
                    // đổ dữ liệu
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[11 + i, 1], exSheet.Cells[11 + i, 9]);
                        rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                        rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                        rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                        rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;

                        exSheet.Cells[11 + i, 1] = "'" + dt.Rows[i]["Ngay"].ToString();
                        decimal TienCongTuKH = decimal.Parse(dt.Rows[i]["TienCongTuKH"].ToString());
                        exSheet.Cells[11 + i, 2] = TienCongTuKH.ToString();
                        Tong_TienCongTuKH += TienCongTuKH;

                        decimal PT_MangVe = decimal.Parse(dt.Rows[i]["PT_MangVe"].ToString());
                        exSheet.Cells[11 + i, 4] = PT_MangVe.ToString();
                        Tong_PT_MangVe += PT_MangVe;

                        decimal Nhot_MangVe = decimal.Parse(dt.Rows[i]["Nhot_MangVe"].ToString());
                        exSheet.Cells[11 + i, 5] = Nhot_MangVe.ToString();
                        Tong_Nhot_MangVe += Nhot_MangVe;

                        decimal PT_TaiCuaHang = decimal.Parse(dt.Rows[i]["PT_TaiCuaHang"].ToString());
                        exSheet.Cells[11 + i, 6] = PT_TaiCuaHang.ToString();
                        Tong_PT_TaiCuaHang += PT_TaiCuaHang;

                        decimal Nhot_TaiCuaHang = decimal.Parse(dt.Rows[i]["Nhot_TaiCuaHang"].ToString());
                        exSheet.Cells[11 + i, 7] = Nhot_TaiCuaHang.ToString();
                        Tong_Nhot_TaiCuaHang += Nhot_TaiCuaHang;

                        decimal Tong = PT_MangVe + Nhot_MangVe + PT_TaiCuaHang + Nhot_TaiCuaHang;
                        exSheet.Cells[11 + i, 8] = Tong.ToString();
                        Tong_Tong += Tong;
                    }
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[11 + dt.Rows.Count, 1], exSheet.Cells[11 + dt.Rows.Count, 9]);
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.Font.Bold = true;
                    exSheet.Cells[11 + dt.Rows.Count, 1] = "Tổng :" + dt.Rows.Count;

                    exSheet.Cells[11 + dt.Rows.Count, 2] = Tong_TienCongTuKH.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 4] = Tong_PT_MangVe.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 5] = Tong_Nhot_MangVe.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 6] = Tong_PT_TaiCuaHang.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 7] = Tong_Nhot_TaiCuaHang.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 8] = Tong_Tong.ToString();

                    exBook.SaveAs(saveFileExcel.FileName, ComExcel.XlFileFormat.xlWorkbookNormal, null, null, false, false, ComExcel.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false);
                    DialogResult dlg = MessageBox.Show("File báo cáo được lưu ở đường dẫn sau: " + saveFileExcel.FileName + Environment.NewLine + "Bạn có muốn mở file không ?", "Thông Báo", MessageBoxButtons.YesNo);
                    if (dlg == DialogResult.Yes)
                    {
                        // hien thi chuong trinh excel
                        exApp.Visible = true;
                        exBook.ReadOnlyRecommended = false;
                    }
                    else
                    {
                        exBook.Close(false, false, false);
                        exApp.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
                    }
                }
            }
        }
        private void BC_SoLuongXeDenKhuVucDichVuTrongNgay()
        {
            DataTable dt = PT_ServiceBillManager.CreateInstant().ReportSoLuongXeDenKhuVucDichVuTrongNgay(fromDate2.Value, toDate2.Value);
            decimal Tong_H08 = 0;
            decimal Tong_H09 = 0;
            decimal Tong_H10 = 0;
            decimal Tong_H11 = 0;
            decimal Tong_H12 = 0;
            decimal Tong_H13 = 0;
            decimal Tong_H14 = 0;
            decimal Tong_H15 = 0;
            decimal Tong_H16 = 0;
            decimal Tong_H17 = 0;
            decimal Tong_H18 = 0;
            decimal Tong_H19 = 0;
            decimal Tong = 0;
            saveFileExcel.FileName = "BC7_SoLuongXeDenKhuVucDichVuTrongNgay_" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm") + ".xls";
            saveFileExcel.DefaultExt = "xls";
            saveFileExcel.Filter = "Excel 2003 Files(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx|All Files|*.*";
            saveFileExcel.AddExtension = true;
            saveFileExcel.RestoreDirectory = true;
            saveFileExcel.Title = "Chọn đường dẫn lưu file";
            saveFileExcel.InitialDirectory = @"C:/";
            if (saveFileExcel.ShowDialog() == DialogResult.OK)
            {
                if (saveFileExcel.FileName.Split('\\')[saveFileExcel.FileName.Split('\\').Length - 1] == ".xls")
                {
                    MessageBox.Show("Nhập tên file cần lưu");
                }
                else
                {
                    // Khoi dong Excel
                    ComExcel.Application exApp = new ComExcel.Application();

                    //// them file temp xls moi
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);

                    // Mo mot file Excel co san
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);
                    ComExcel.Workbook exBook = exApp.Workbooks.Add(Application.StartupPath + "/ExcelTemplate/BC7.xls");

                    // Lay sheet 1
                    ComExcel.Worksheet exSheet = (ComExcel.Worksheet)exBook.Worksheets[1];
                    //exSheet.Activate();

                    // dia chi 
                    DataTable headdt = PT_ServiceBillManager.CreateInstant().GetReportHead(SessionLogin.UserId).Tables[0];
                    exSheet.Cells[3, 1] = headdt.Rows[0]["HEAD_ADD"];

                    //tu ngay- den ngay - dong 6 , cot 9
                    exSheet.Cells[6, 1] = "Từ ngày: " + fromDate2.Value.ToString("dd/MM/yyyy") + " đến ngày: " + toDate2.Value.ToString("dd/MM/yyyy");

                    ////cau hình chung
                    //ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 1], exSheet.Cells[8 + dt.Rows.Count, HondaTypeList.Count]);
                    //rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    //rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    //rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    //rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[10, 1], exSheet.Cells[1000, 27]);
                    rng.Font.Size = 9;
                    rng.WrapText = true;
                    // đổ dữ liệu
                    if (dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[10 + i, 1], exSheet.Cells[10 + i, 27]);
                            rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                            rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                            rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                            rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                            decimal TongLuotXe = decimal.Parse(dt.Rows[i]["Tong"].ToString());
                            Tong += TongLuotXe;
                            exSheet.Cells[10 + i, 1] = (i + 1).ToString();
                            exSheet.Cells[10 + i, 2] = dt.Rows[i]["Ngay"].ToString();

                            DateTime date = AF.Library.FDateTime.ConvertDate(dt.Rows[i]["Ngay"].ToString());
                            exSheet.Cells[10 + i, 3] = AF.Library.FDateTime.GetDayName(date.DayOfWeek);

                            decimal H08 = decimal.Parse(dt.Rows[i]["H08"].ToString());
                            exSheet.Cells[10 + i, 4] = H08.ToString();
                            Tong_H08 += H08;

                            exSheet.Cells[10 + i, 5] = "'" + Math.Round((H08 / TongLuotXe) * 100, 2) + "%";

                            decimal H09 = decimal.Parse(dt.Rows[i]["H09"].ToString());
                            exSheet.Cells[10 + i, 6] = H09.ToString();
                            Tong_H09 += H09;

                            exSheet.Cells[10 + i, 7] = "'" + Math.Round((H09 / TongLuotXe) * 100, 2) + "%";

                            decimal H10 = decimal.Parse(dt.Rows[i]["H10"].ToString());
                            exSheet.Cells[10 + i, 8] = H10.ToString();
                            Tong_H10 += H10;

                            exSheet.Cells[10 + i, 9] = "'" + Math.Round((H10 / TongLuotXe) * 100, 2) + "%";

                            decimal H11 = decimal.Parse(dt.Rows[i]["H11"].ToString());
                            exSheet.Cells[10 + i, 10] = H11.ToString();
                            Tong_H11 += H11;

                            exSheet.Cells[10 + i, 11] = "'" + Math.Round((H11 / TongLuotXe) * 100, 2) + "%";

                            decimal H12 = decimal.Parse(dt.Rows[i]["H12"].ToString());
                            exSheet.Cells[10 + i, 12] = H12.ToString();
                            Tong_H12 += H12;

                            exSheet.Cells[10 + i, 13] = "'" + Math.Round((H12 / TongLuotXe) * 100, 2) + "%";

                            decimal H13 = decimal.Parse(dt.Rows[i]["H13"].ToString());
                            exSheet.Cells[10 + i, 14] = H13.ToString();
                            Tong_H13 += H13;

                            exSheet.Cells[10 + i, 15] = "'" + Math.Round((H13 / TongLuotXe) * 100, 2) + "%";

                            decimal H14 = decimal.Parse(dt.Rows[i]["H14"].ToString());
                            exSheet.Cells[10 + i, 16] = H14.ToString();
                            Tong_H14 += H14;

                            exSheet.Cells[10 + i, 17] = "'" + Math.Round((H14 / TongLuotXe) * 100, 2) + "%";

                            decimal H15 = decimal.Parse(dt.Rows[i]["H15"].ToString());
                            exSheet.Cells[10 + i, 18] = H15.ToString();
                            Tong_H15 += H15;

                            exSheet.Cells[10 + i, 19] = "'" + Math.Round((H15 / TongLuotXe) * 100, 2) + "%";

                            decimal H16 = decimal.Parse(dt.Rows[i]["H16"].ToString());
                            exSheet.Cells[10 + i, 20] = H16.ToString();
                            Tong_H16 += H16;

                            exSheet.Cells[10 + i, 21] = "'" + Math.Round((H16 / TongLuotXe) * 100, 2) + "%";

                            decimal H17 = decimal.Parse(dt.Rows[i]["H17"].ToString());
                            exSheet.Cells[10 + i, 22] = H17.ToString();
                            Tong_H17 += H17;

                            exSheet.Cells[10 + i, 23] = "'" + Math.Round((H17 / TongLuotXe) * 100, 2) + "%";

                            decimal H18 = decimal.Parse(dt.Rows[i]["H18"].ToString());
                            exSheet.Cells[10 + i, 24] = H18.ToString();
                            Tong_H18 += H18;

                            exSheet.Cells[10 + i, 25] = "'" + Math.Round((H18 / TongLuotXe) * 100, 2) + "%";

                            decimal H19 = decimal.Parse(dt.Rows[i]["H19"].ToString());
                            exSheet.Cells[10 + i, 26] = H19.ToString();
                            Tong_H19 += H19;

                            exSheet.Cells[10 + i, 27] = "'" + Math.Round((H19 / TongLuotXe) * 100, 2) + "%";
                        }
                        Tong = Tong_H08 + Tong_H09 + Tong_H10 + Tong_H11 + Tong_H12 + Tong_H13 + Tong_H14 + Tong_H15 + Tong_H16 + Tong_H17 + Tong_H18 + Tong_H19;
                        rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[10 + dt.Rows.Count, 1], exSheet.Cells[10 + dt.Rows.Count, 27]);
                        rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                        rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                        rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                        rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                        rng.Font.Bold = true;
                        exSheet.Cells[10 + dt.Rows.Count, 1] = "Tổng:";
                        exSheet.Cells[10 + dt.Rows.Count, 2] = "";
                        exSheet.Cells[10 + dt.Rows.Count, 3] = "";

                        exSheet.Cells[10 + dt.Rows.Count, 4] = Tong_H08.ToString();

                        exSheet.Cells[10 + dt.Rows.Count, 5] = "'" + Math.Round((Tong_H08 / Tong) * 100, 2) + "%";

                        exSheet.Cells[10 + dt.Rows.Count, 6] = Tong_H09.ToString();

                        exSheet.Cells[10 + dt.Rows.Count, 7] = "'" + Math.Round((Tong_H09 / Tong) * 100, 2) + "%";

                        exSheet.Cells[10 + dt.Rows.Count, 8] = Tong_H10.ToString();

                        exSheet.Cells[10 + dt.Rows.Count, 9] = "'" + Math.Round((Tong_H10 / Tong) * 100, 2) + "%";

                        exSheet.Cells[10 + dt.Rows.Count, 10] = Tong_H11.ToString();

                        exSheet.Cells[10 + dt.Rows.Count, 11] = "'" + Math.Round((Tong_H11 / Tong) * 100, 2) + "%";

                        exSheet.Cells[10 + dt.Rows.Count, 12] = Tong_H12.ToString();

                        exSheet.Cells[10 + dt.Rows.Count, 13] = "'" + Math.Round((Tong_H12 / Tong) * 100, 2) + "%";

                        exSheet.Cells[10 + dt.Rows.Count, 14] = Tong_H13.ToString();

                        exSheet.Cells[10 + dt.Rows.Count, 15] = "'" + Math.Round((Tong_H13 / Tong) * 100, 2) + "%";

                        exSheet.Cells[10 + dt.Rows.Count, 16] = Tong_H14.ToString();

                        exSheet.Cells[10 + dt.Rows.Count, 17] = "'" + Math.Round((Tong_H14 / Tong) * 100, 2) + "%";

                        exSheet.Cells[10 + dt.Rows.Count, 18] = Tong_H15.ToString();

                        exSheet.Cells[10 + dt.Rows.Count, 19] = "'" + Math.Round((Tong_H15 / Tong) * 100, 2) + "%";

                        exSheet.Cells[10 + dt.Rows.Count, 20] = Tong_H16.ToString();

                        exSheet.Cells[10 + dt.Rows.Count, 21] = "'" + Math.Round((Tong_H16 / Tong) * 100, 2) + "%";

                        exSheet.Cells[10 + dt.Rows.Count, 22] = Tong_H17.ToString();

                        exSheet.Cells[10 + dt.Rows.Count, 23] = "'" + Math.Round((Tong_H17 / Tong) * 100, 2) + "%";

                        exSheet.Cells[10 + dt.Rows.Count, 24] = Tong_H18.ToString();

                        exSheet.Cells[10 + dt.Rows.Count, 25] = "'" + Math.Round((Tong_H18 / Tong) * 100, 2) + "%";

                        exSheet.Cells[10 + dt.Rows.Count, 26] = Tong_H19.ToString();

                        exSheet.Cells[10 + dt.Rows.Count, 27] = "'" + Math.Round((Tong_H19 / Tong) * 100, 2) + "%";
                    }

                    exBook.SaveAs(saveFileExcel.FileName, ComExcel.XlFileFormat.xlWorkbookNormal, null, null, false, false, ComExcel.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false);
                    DialogResult dlg = MessageBox.Show("File báo cáo được lưu ở đường dẫn sau: " + saveFileExcel.FileName + Environment.NewLine + "Bạn có muốn mở file không ?", "Thông Báo", MessageBoxButtons.YesNo);
                    if (dlg == DialogResult.Yes)
                    {
                        // hien thi chuong trinh excel
                        exApp.Visible = true;
                        exBook.ReadOnlyRecommended = false;
                    }
                    else
                    {
                        exBook.Close(false, false, false);
                        exApp.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
                    }
                }
            }
        }
        private void BC_KeToan_TongHopDoanhThuDichVuPhuTungNhot()
        {
            DataTable dt = PT_ServiceBillManager.CreateInstant().ReportTongHopDoanhThuDichVuPhuTungNhot(fromDate2.Value, toDate2.Value);

            decimal Tong_DV_DoanhThuSuaChua = 0;
            decimal Tong_DV_ThuGCN = 0;
            decimal Tong_DV_ChiGCN = 0;
            decimal Tong_DV_LaiGCN = 0;
            decimal Tong_DV_TongDoanhThu = 0;
            decimal Tong_PT_Thu_Nhot = 0;
            decimal Tong_PT_MV_Nhot = 0;
            decimal Tong_TongNhot = 0;
            decimal Tong_PT_Thu_PTT = 0;
            decimal Tong_PT_MV_PTT = 0;
            decimal Tong_Tong_PTT = 0;
            decimal Tong_PT_Thu_PTN = 0;
            decimal Tong_PT_MV_PTN = 0;
            decimal Tong_Tong_PTN = 0;
            decimal Tong_PT_Thu_PTTM = 0;
            decimal Tong_PT_Chi_PTTM = 0;
            decimal Tong_PT_Lai_PTTM = 0;
            decimal Tong_TongPhuTung = 0;
            saveFileExcel.FileName = "BC8_KeToan_TongHopDoanhThuDichVuPhuTungNhot_" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm") + ".xls";
            saveFileExcel.DefaultExt = "xls";
            saveFileExcel.Filter = "Excel 2003 Files(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx|All Files|*.*";
            saveFileExcel.AddExtension = true;
            saveFileExcel.RestoreDirectory = true;
            saveFileExcel.Title = "Chọn đường dẫn lưu file";
            saveFileExcel.InitialDirectory = @"C:/";
            if (saveFileExcel.ShowDialog() == DialogResult.OK)
            {
                if (saveFileExcel.FileName.Split('\\')[saveFileExcel.FileName.Split('\\').Length - 1] == ".xls")
                {
                    MessageBox.Show("Nhập tên file cần lưu");
                }
                else
                {
                    // Khoi dong Excel
                    ComExcel.Application exApp = new ComExcel.Application();

                    //// them file temp xls moi
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);

                    // Mo mot file Excel co san
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);
                    ComExcel.Workbook exBook = exApp.Workbooks.Add(Application.StartupPath + "/ExcelTemplate/BC8.xls");

                    // Lay sheet 1
                    ComExcel.Worksheet exSheet = (ComExcel.Worksheet)exBook.Worksheets[1];
                    //exSheet.Activate();

                    // dia chi 
                    DataTable headdt = PT_ServiceBillManager.CreateInstant().GetReportHead(SessionLogin.UserId).Tables[0];
                    exSheet.Cells[3, 1] = headdt.Rows[0]["HEAD_ADD"];

                    //tu ngay- den ngay - dong 6 , cot 9
                    exSheet.Cells[6, 1] = "Từ ngày: " + fromDate2.Value.ToString("dd/MM/yyyy") + " đến ngày: " + toDate2.Value.ToString("dd/MM/yyyy");

                    ////cau hình chung
                    //ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 1], exSheet.Cells[8 + dt.Rows.Count, HondaTypeList.Count]);
                    //rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    //rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    //rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    //rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[11, 1], exSheet.Cells[1000, 20]);
                    rng.Font.Size = 9;
                    rng.WrapText = true;
                    // đổ dữ liệu
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[11 + i, 1], exSheet.Cells[11 + i, 20]);
                        rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                        rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                        rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                        rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;

                        exSheet.Cells[11 + i, 1] = (i + 1).ToString();
                        exSheet.Cells[11 + i, 2] = dt.Rows[i]["Ngay"].ToString();

                        decimal DV_DoanhThuSuaChua = decimal.Parse(dt.Rows[i]["DV_DoanhThuSuaChua"].ToString());
                        exSheet.Cells[11 + i, 3] = DV_DoanhThuSuaChua.ToString();
                        Tong_DV_DoanhThuSuaChua += DV_DoanhThuSuaChua;

                        decimal DV_ThuGCN = decimal.Parse(dt.Rows[i]["DV_ThuGCN"].ToString());
                        exSheet.Cells[11 + i, 4] = DV_ThuGCN.ToString();
                        Tong_DV_ThuGCN += DV_ThuGCN;

                        decimal DV_ChiGCN = decimal.Parse(dt.Rows[i]["DV_ChiGCN"].ToString());
                        exSheet.Cells[11 + i, 5] = DV_ChiGCN.ToString();
                        Tong_DV_ChiGCN += DV_ChiGCN;

                        decimal DV_LaiGCN = decimal.Parse(dt.Rows[i]["DV_LaiGCN"].ToString());
                        exSheet.Cells[11 + i, 6] = DV_LaiGCN.ToString();
                        Tong_DV_LaiGCN += DV_LaiGCN;

                        decimal DV_TongDoanhThu = decimal.Parse(dt.Rows[i]["DV_TongDoanhThu"].ToString());
                        exSheet.Cells[11 + i, 7] = DV_TongDoanhThu.ToString();
                        Tong_DV_TongDoanhThu += DV_TongDoanhThu;

                        decimal PT_Thu_Nhot = decimal.Parse(dt.Rows[i]["PT_Thu_Nhot"].ToString());
                        exSheet.Cells[11 + i, 8] = PT_Thu_Nhot.ToString();
                        Tong_PT_Thu_Nhot += PT_Thu_Nhot;

                        decimal PT_MV_Nhot = decimal.Parse(dt.Rows[i]["PT_MV_Nhot"].ToString());
                        exSheet.Cells[11 + i, 9] = PT_MV_Nhot.ToString();
                        Tong_PT_MV_Nhot += PT_MV_Nhot;

                        decimal TongNhot = decimal.Parse(dt.Rows[i]["TongNhot"].ToString());
                        exSheet.Cells[11 + i, 10] = TongNhot.ToString();
                        Tong_TongNhot += TongNhot;

                        decimal PT_Thu_PTT = decimal.Parse(dt.Rows[i]["PT_Thu_PTT"].ToString());
                        exSheet.Cells[11 + i, 11] = PT_Thu_PTT.ToString();
                        Tong_PT_Thu_PTT += PT_Thu_PTT;

                        decimal PT_MV_PTT = decimal.Parse(dt.Rows[i]["PT_MV_PTT"].ToString());
                        exSheet.Cells[11 + i, 12] = PT_MV_PTT.ToString();
                        Tong_PT_MV_PTT += PT_MV_PTT;

                        decimal Tong_PTT = decimal.Parse(dt.Rows[i]["Tong_PTT"].ToString());
                        exSheet.Cells[11 + i, 13] = Tong_PTT.ToString();
                        Tong_Tong_PTT += Tong_PTT;

                        decimal PT_Thu_PTN = decimal.Parse(dt.Rows[i]["PT_Thu_PTN"].ToString());
                        exSheet.Cells[11 + i, 14] = PT_Thu_PTN.ToString();
                        Tong_PT_Thu_PTN += PT_Thu_PTN;

                        decimal PT_MV_PTN = decimal.Parse(dt.Rows[i]["PT_MV_PTN"].ToString());
                        exSheet.Cells[11 + i, 15] = PT_MV_PTN.ToString();
                        Tong_PT_MV_PTN += PT_MV_PTN;

                        decimal Tong_PTN = decimal.Parse(dt.Rows[i]["Tong_PTN"].ToString());
                        exSheet.Cells[11 + i, 16] = Tong_PTN.ToString();
                        Tong_Tong_PTN += Tong_PTN;

                        decimal PT_Thu_PTTM = decimal.Parse(dt.Rows[i]["PT_Thu_PTTM"].ToString());
                        exSheet.Cells[11 + i, 17] = PT_Thu_PTTM.ToString();
                        Tong_PT_Thu_PTTM += PT_Thu_PTTM;

                        decimal PT_Chi_PTTM = decimal.Parse(dt.Rows[i]["PT_Chi_PTTM"].ToString());
                        exSheet.Cells[11 + i, 18] = PT_Chi_PTTM.ToString();
                        Tong_PT_Chi_PTTM += PT_Chi_PTTM;

                        decimal PT_Lai_PTTM = decimal.Parse(dt.Rows[i]["PT_Lai_PTTM"].ToString());
                        exSheet.Cells[11 + i, 19] = PT_Lai_PTTM.ToString();
                        Tong_PT_Lai_PTTM += PT_Lai_PTTM;

                        decimal TongPhuTung = Tong_PTT + Tong_PTN + PT_Lai_PTTM + TongNhot;
                        exSheet.Cells[11 + i, 20] = TongPhuTung.ToString();
                        Tong_TongPhuTung += TongPhuTung;
                    }
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[11 + dt.Rows.Count, 1], exSheet.Cells[11 + dt.Rows.Count, 20]);
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.Font.Bold = true;
                    exSheet.Cells[11 + dt.Rows.Count, 1] = "Tổng cộng: ";
                    //exSheet.Cells[11 + dt.Rows.Count, 2] = "";

                    exSheet.Cells[11 + dt.Rows.Count, 3] = Tong_DV_DoanhThuSuaChua.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 4] = Tong_DV_ThuGCN.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 5] = Tong_DV_ChiGCN.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 6] = Tong_DV_LaiGCN.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 7] = Tong_DV_TongDoanhThu.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 8] = Tong_PT_Thu_Nhot.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 9] = Tong_PT_MV_Nhot.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 10] = Tong_TongNhot.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 11] = Tong_PT_Thu_PTT.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 12] = Tong_PT_MV_PTT.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 13] = Tong_Tong_PTT.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 14] = Tong_PT_Thu_PTN.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 15] = Tong_PT_MV_PTN.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 16] = Tong_Tong_PTN.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 17] = Tong_PT_Thu_PTTM.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 18] = Tong_PT_Chi_PTTM.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 19] = Tong_PT_Lai_PTTM.ToString();

                    exSheet.Cells[11 + dt.Rows.Count, 20] = Tong_TongPhuTung.ToString();

                    exBook.SaveAs(saveFileExcel.FileName, ComExcel.XlFileFormat.xlWorkbookNormal, null, null, false, false, ComExcel.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false);
                    DialogResult dlg = MessageBox.Show("File báo cáo được lưu ở đường dẫn sau: " + saveFileExcel.FileName + Environment.NewLine + "Bạn có muốn mở file không ?", "Thông Báo", MessageBoxButtons.YesNo);
                    if (dlg == DialogResult.Yes)
                    {
                        // hien thi chuong trinh excel
                        exApp.Visible = true;
                        exBook.ReadOnlyRecommended = false;
                    }
                    else
                    {
                        exBook.Close(false, false, false);
                        exApp.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
                    }
                }
            }
        }
        private void BC_KeToan_DoanhThuDichVuPhuTungNhotChiTiet()
        {
            DataTable dt = PT_ServiceBillManager.CreateInstant().ReportTongHopDoanhThuDichVuPhuTungNhotChiTiet(fromDate2.Value, toDate2.Value);
            decimal Tong_DV_SoLuotRB = 0;
            decimal Tong_DV_SoLuotKTDK = 0;
            decimal Tong_SoLuotSuaChuaNgoaiKTDK = 0;
            decimal Tong_DV_TongLuotXe = 0;
            decimal Tong_ThoiGianRB = 0;
            decimal Tong_ThoiGianKTDK = 0;
            decimal Tong_DV_ThoiGianSua = 0;
            decimal Tong_TongThoiGianSua = 0;
            decimal Tong_DV_DoanhSoRB = 0;
            decimal Tong_DV_DoanhSoKTDK = 0;
            decimal Tong_DV_ThuGCN = 0;
            decimal Tong_DV_ChiGCN = 0;
            decimal Tong_DV_LaiGCN = 0;
            decimal Tong_DV_SC_GiaBan = 0;
            decimal Tong_DV_SC_GiamGia = 0;
            decimal Tong_DV_SC_ThucThu = 0;
            decimal Tong_DV_SuaChua_BaoHanh = 0;
            decimal Tong_DV_SuaChua_ThienChi = 0;
            decimal Tong_DV_SuaChua_KhuyenMai = 0;
            decimal Tong_DS_DV_CoThuTien = 0;
            decimal Tong_DS_DV_KhongThuTien = 0;
            decimal Tong_TongDSDV = 0;
            decimal Tong_PT_GiaBan_Nhot = 0;
            decimal Tong_PT_GiamGia_Nhot = 0;
            decimal Tong_PT_Thu_Nhot = 0;
            decimal Tong_PT_Nhot_BaoHanh = 0;
            decimal Tong_PT_Nhot_ThienChi = 0;
            decimal Tong_PT_Nhot_KhuyenMai = 0;
            decimal Tong_PT_MV_Nhot_GiaBan = 0;
            decimal Tong_PT_MV_Nhot_GiamGia = 0;
            decimal Tong_PT_MV_Nhot_ThucThu = 0;
            decimal Tong_PT_PTT_GiaBan = 0;
            decimal Tong_PT_PTT_GiamGia = 0;
            decimal Tong_PT_Thu_PTT = 0;
            decimal Tong_PT_PTT_BaoHanh = 0;
            decimal Tong_PT_PTT_ThienChi = 0;
            decimal Tong_PT_PTT_KhuyenMai = 0;
            decimal Tong_PT_MV_PTT_GiaBan = 0;
            decimal Tong_PT_MV_PTT_GiamGia = 0;
            decimal Tong_PT_MV_PTT_ThucThu = 0;
            decimal Tong_PT_PTN_GiaBan = 0;
            decimal Tong_PT_PTN_GiamGia = 0;
            decimal Tong_PT_Thu_PTN = 0;
            decimal Tong_PT_PTN_BaoHanh = 0;
            decimal Tong_PT_PTN_ThienChi = 0;
            decimal Tong_PT_PTN_KhuyenMai = 0;
            decimal Tong_PT_MV_PTN_GiaBan = 0;
            decimal Tong_PT_MV_PTN_GiamGia = 0;
            decimal Tong_PT_MV_PTN_ThucThu = 0;
            decimal Tong_PT_Thu_PTTM = 0;
            decimal Tong_PT_Chi_PTTM = 0;
            decimal Tong_PT_Lai_PTTM = 0;
            decimal Tong_DSPT_CoThuTien = 0;
            decimal Tong_DSPT_KhongThuTien = 0;
            decimal Tong_TongDSPT = 0;

            saveFileExcel.FileName = "BC9_KeToan_DoanhThuDichVuPhuTungNhotChiTiet_" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm") + ".xls";
            saveFileExcel.DefaultExt = "xls";
            saveFileExcel.Filter = "Excel 2003 Files(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx|All Files|*.*";
            saveFileExcel.AddExtension = true;
            saveFileExcel.RestoreDirectory = true;
            saveFileExcel.Title = "Chọn đường dẫn lưu file";
            saveFileExcel.InitialDirectory = @"C:/";
            if (saveFileExcel.ShowDialog() == DialogResult.OK)
            {
                if (saveFileExcel.FileName.Split('\\')[saveFileExcel.FileName.Split('\\').Length - 1] == ".xls")
                {
                    MessageBox.Show("Nhập tên file cần lưu");
                }
                else
                {
                   // ShowStatusBar("Đang xuất dữ liệu excel");
                    // Khoi dong Excel
                    ComExcel.Application exApp = new ComExcel.Application();

                    //// them file temp xls moi
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);

                    // Mo mot file Excel co san
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);
                    ComExcel.Workbook exBook = exApp.Workbooks.Add(Application.StartupPath + "/ExcelTemplate/BC9.xls");

                    // Lay sheet 1
                    ComExcel.Worksheet exSheet = (ComExcel.Worksheet)exBook.Worksheets[1];
                    //exSheet.Activate();

                    // dia chi 
                    DataTable headdt = PT_ServiceBillManager.CreateInstant().GetReportHead(SessionLogin.UserId).Tables[0];
                    exSheet.Cells[3, 1] = headdt.Rows[0]["HEAD_ADD"];

                    //tu ngay- den ngay - dong 6 , cot 9
                    exSheet.Cells[6, 1] = "Từ ngày: " + fromDate2.Value.ToString("dd/MM/yyyy") + " đến ngày: " + toDate2.Value.ToString("dd/MM/yyyy");

                    ////cau hình chung
                    //ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 1], exSheet.Cells[8 + dt.Rows.Count, HondaTypeList.Count]);
                    //rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    //rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    //rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    //rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[12, 1], exSheet.Cells[1000, 57]);
                    rng.Font.Size = 9;
                    rng.WrapText = true;
                    // đổ dữ liệu
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[12 + i, 1], exSheet.Cells[12 + i, 57]);
                        rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                        rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                        rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                        rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;

                        exSheet.Cells[12 + i, 1] = (i + 1).ToString();
                        exSheet.Cells[12 + i, 2] = dt.Rows[i]["Ngay"];

                        decimal DV_SoLuotRB = decimal.Parse(dt.Rows[i]["DV_SoLuotRB"].ToString());
                        exSheet.Cells[12 + i, 3] = DV_SoLuotRB.ToString();
                        Tong_DV_SoLuotRB += DV_SoLuotRB;

                        decimal DV_SoLuotKTDK = decimal.Parse(dt.Rows[i]["DV_SoLuotKTDK"].ToString());
                        exSheet.Cells[12 + i, 4] = DV_SoLuotKTDK.ToString();
                        Tong_DV_SoLuotKTDK += DV_SoLuotKTDK;

                        decimal SoLuotSuaChuaNgoaiKTDK = decimal.Parse(dt.Rows[i]["SoLuotSuaChuaNgoaiKTDK"].ToString());
                        exSheet.Cells[12 + i, 5] = SoLuotSuaChuaNgoaiKTDK.ToString();
                        Tong_SoLuotSuaChuaNgoaiKTDK += SoLuotSuaChuaNgoaiKTDK;

                        decimal DV_TongLuotXe = decimal.Parse(dt.Rows[i]["DV_TongLuotXe"].ToString());
                        exSheet.Cells[12 + i, 6] = DV_TongLuotXe.ToString();
                        Tong_DV_TongLuotXe += DV_TongLuotXe;

                        decimal ThoiGianRB = decimal.Parse(dt.Rows[i]["ThoiGianRB"].ToString());
                        exSheet.Cells[12 + i, 7] = ThoiGianRB.ToString();
                        Tong_ThoiGianRB += ThoiGianRB;

                        decimal ThoiGianKTDK = decimal.Parse(dt.Rows[i]["ThoiGianKTDK"].ToString());
                        exSheet.Cells[12 + i, 8] = ThoiGianKTDK.ToString();
                        Tong_ThoiGianKTDK += ThoiGianKTDK;

                        decimal DV_ThoiGianSua = decimal.Parse(dt.Rows[i]["DV_ThoiGianSua"].ToString());
                        exSheet.Cells[12 + i, 9] = DV_ThoiGianSua.ToString();
                        Tong_DV_ThoiGianSua += DV_ThoiGianSua;

                        decimal TongThoiGianSua = decimal.Parse(dt.Rows[i]["TongThoiGianSua"].ToString());
                        exSheet.Cells[12 + i, 10] = TongThoiGianSua.ToString();
                        Tong_TongThoiGianSua += TongThoiGianSua;

                        decimal DV_DoanhSoRB = decimal.Parse(dt.Rows[i]["DV_DoanhSoRB"].ToString());
                        exSheet.Cells[12 + i, 11] = DV_DoanhSoRB.ToString();
                        Tong_DV_DoanhSoRB += DV_DoanhSoRB;

                        decimal DV_DoanhSoKTDK = decimal.Parse(dt.Rows[i]["DV_DoanhSoKTDK"].ToString());
                        exSheet.Cells[12 + i, 12] = DV_DoanhSoKTDK.ToString();
                        Tong_DV_DoanhSoKTDK += DV_DoanhSoKTDK;

                        decimal DV_ThuGCN = decimal.Parse(dt.Rows[i]["DV_ThuGCN"].ToString());
                        exSheet.Cells[12 + i, 13] = DV_ThuGCN.ToString();
                        Tong_DV_ThuGCN += DV_ThuGCN;

                        decimal DV_ChiGCN = decimal.Parse(dt.Rows[i]["DV_ChiGCN"].ToString());
                        exSheet.Cells[12 + i, 14] = DV_ChiGCN.ToString();
                        Tong_DV_ChiGCN += DV_ChiGCN;

                        decimal DV_LaiGCN = decimal.Parse(dt.Rows[i]["DV_LaiGCN"].ToString());
                        exSheet.Cells[12 + i, 15] = DV_LaiGCN.ToString();
                        Tong_DV_LaiGCN += DV_LaiGCN;

                        decimal DV_SC_GiaBan = decimal.Parse(dt.Rows[i]["DV_SC_GiaBan"].ToString());
                        exSheet.Cells[12 + i, 16] = DV_SC_GiaBan.ToString();
                        Tong_DV_SC_GiaBan += DV_SC_GiaBan;

                        decimal DV_SC_GiamGia = decimal.Parse(dt.Rows[i]["DV_SC_GiamGia"].ToString());
                        exSheet.Cells[12 + i, 17] = DV_SC_GiamGia.ToString();
                        Tong_DV_SC_GiamGia += DV_SC_GiamGia;

                        decimal DV_SC_ThucThu = decimal.Parse(dt.Rows[i]["DV_SC_ThucThu"].ToString());
                        exSheet.Cells[12 + i, 18] = DV_SC_ThucThu.ToString();
                        Tong_DV_SC_ThucThu += DV_SC_ThucThu;

                        decimal DV_SuaChua_BaoHanh = decimal.Parse(dt.Rows[i]["DV_SuaChua_BaoHanh"].ToString());
                        exSheet.Cells[12 + i, 19] = DV_SuaChua_BaoHanh.ToString();
                        Tong_DV_SuaChua_BaoHanh += DV_SuaChua_BaoHanh;

                        decimal DV_SuaChua_ThienChi = decimal.Parse(dt.Rows[i]["DV_SuaChua_ThienChi"].ToString());
                        exSheet.Cells[12 + i, 20] = DV_SuaChua_ThienChi.ToString();
                        Tong_DV_SuaChua_ThienChi += DV_SuaChua_ThienChi;

                        decimal DV_SuaChua_KhuyenMai = decimal.Parse(dt.Rows[i]["DV_SuaChua_KhuyenMai"].ToString());
                        exSheet.Cells[12 + i, 21] = DV_SuaChua_KhuyenMai.ToString();
                        Tong_DV_SuaChua_KhuyenMai += DV_SuaChua_KhuyenMai;

                        decimal DS_DV_CoThuTien = DV_LaiGCN + DV_SC_ThucThu;
                        exSheet.Cells[12 + i, 22] = DS_DV_CoThuTien.ToString();
                        Tong_DS_DV_CoThuTien += DS_DV_CoThuTien;

                        decimal DS_DV_KhongThuTien = DV_DoanhSoRB + DV_DoanhSoKTDK + DV_SC_GiamGia + DV_SuaChua_BaoHanh + DV_SuaChua_ThienChi + DV_SuaChua_KhuyenMai;
                        exSheet.Cells[12 + i, 23] = DS_DV_KhongThuTien.ToString();
                        Tong_DS_DV_KhongThuTien += DS_DV_KhongThuTien;

                        decimal TongDSDV = DS_DV_CoThuTien + DS_DV_KhongThuTien;
                        exSheet.Cells[12 + i, 24] = TongDSDV.ToString();
                        Tong_TongDSDV += TongDSDV;

                        decimal PT_GiaBan_Nhot = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_GiaBan_Nhot"].ToString()) ? dt.Rows[i]["PT_GiaBan_Nhot"].ToString() : "0");
                        exSheet.Cells[12 + i, 25] = PT_GiaBan_Nhot.ToString();
                        Tong_PT_GiaBan_Nhot += PT_GiaBan_Nhot;

                        decimal PT_GiamGia_Nhot = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_GiamGia_Nhot"].ToString()) ? dt.Rows[i]["PT_GiamGia_Nhot"].ToString() : "0");
                        exSheet.Cells[12 + i, 26] = PT_GiamGia_Nhot.ToString();
                        Tong_PT_GiamGia_Nhot += PT_GiamGia_Nhot;

                        decimal PT_Thu_Nhot = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_Thu_Nhot"].ToString()) ? dt.Rows[i]["PT_Thu_Nhot"].ToString() : "0");
                        exSheet.Cells[12 + i, 27] = PT_Thu_Nhot.ToString();
                        Tong_PT_Thu_Nhot += PT_Thu_Nhot;

                        decimal PT_Nhot_BaoHanh = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_Nhot_BaoHanh"].ToString()) ? dt.Rows[i]["PT_Nhot_BaoHanh"].ToString() : "0");
                        exSheet.Cells[12 + i, 28] = PT_Nhot_BaoHanh.ToString();
                        Tong_PT_Nhot_BaoHanh += PT_Nhot_BaoHanh;

                        decimal PT_Nhot_ThienChi = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_Nhot_ThienChi"].ToString()) ? dt.Rows[i]["PT_Nhot_ThienChi"].ToString() : "0");
                        exSheet.Cells[12 + i, 29] = PT_Nhot_ThienChi.ToString();
                        Tong_PT_Nhot_ThienChi += PT_Nhot_ThienChi;

                        decimal PT_Nhot_KhuyenMai = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_Nhot_KhuyenMai"].ToString()) ? dt.Rows[i]["PT_Nhot_KhuyenMai"].ToString() : "0");
                        exSheet.Cells[12 + i, 30] = PT_Nhot_KhuyenMai.ToString();
                        Tong_PT_Nhot_KhuyenMai += PT_Nhot_KhuyenMai;

                        decimal PT_MV_Nhot_GiaBan = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_MV_Nhot_GiaBan"].ToString()) ? dt.Rows[i]["PT_MV_Nhot_GiaBan"].ToString() : "0");
                        exSheet.Cells[12 + i, 31] = PT_MV_Nhot_GiaBan.ToString();
                        Tong_PT_MV_Nhot_GiaBan += PT_MV_Nhot_GiaBan;

                        decimal PT_MV_Nhot_GiamGia = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_MV_Nhot_GiamGia"].ToString()) ? dt.Rows[i]["PT_MV_Nhot_GiamGia"].ToString() : "0");
                        exSheet.Cells[12 + i, 32] = PT_MV_Nhot_GiamGia.ToString();
                        Tong_PT_MV_Nhot_GiamGia += PT_MV_Nhot_GiamGia;

                        decimal PT_MV_Nhot_ThucThu = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_MV_Nhot_ThucThu"].ToString()) ? dt.Rows[i]["PT_MV_Nhot_ThucThu"].ToString() : "0");
                        exSheet.Cells[12 + i, 33] = PT_MV_Nhot_ThucThu.ToString();
                        Tong_PT_MV_Nhot_ThucThu += PT_MV_Nhot_ThucThu;

                        decimal PT_PTT_GiaBan = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_PTT_GiaBan"].ToString()) ? dt.Rows[i]["PT_PTT_GiaBan"].ToString() : "0");
                        exSheet.Cells[12 + i, 34] = PT_PTT_GiaBan.ToString();
                        Tong_PT_PTT_GiaBan += PT_PTT_GiaBan;

                        decimal PT_PTT_GiamGia = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_PTT_GiamGia"].ToString()) ? dt.Rows[i]["PT_PTT_GiamGia"].ToString() : "0");
                        exSheet.Cells[12 + i, 35] = PT_PTT_GiamGia.ToString();
                        Tong_PT_PTT_GiamGia += PT_PTT_GiamGia;

                        decimal PT_Thu_PTT = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_Thu_PTT"].ToString()) ? dt.Rows[i]["PT_Thu_PTT"].ToString() : "0");
                        exSheet.Cells[12 + i, 36] = PT_Thu_PTT.ToString();
                        Tong_PT_Thu_PTT += PT_Thu_PTT;

                        decimal PT_PTT_BaoHanh = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_PTT_BaoHanh"].ToString()) ? dt.Rows[i]["PT_PTT_BaoHanh"].ToString() : "0");
                        exSheet.Cells[12 + i, 37] = PT_PTT_BaoHanh.ToString();
                        Tong_PT_PTT_BaoHanh += PT_PTT_BaoHanh;

                        decimal PT_PTT_ThienChi = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_PTT_ThienChi"].ToString()) ? dt.Rows[i]["PT_PTT_ThienChi"].ToString() : "0");
                        exSheet.Cells[12 + i, 38] = PT_PTT_ThienChi.ToString();
                        Tong_PT_PTT_ThienChi += PT_PTT_ThienChi;

                        decimal PT_PTT_KhuyenMai = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_PTT_KhuyenMai"].ToString()) ? dt.Rows[i]["PT_PTT_KhuyenMai"].ToString() : "0");
                        exSheet.Cells[12 + i, 39] = PT_PTT_KhuyenMai.ToString();
                        Tong_PT_PTT_KhuyenMai += PT_PTT_KhuyenMai;

                        decimal PT_MV_PTT_GiaBan = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_MV_PTT_GiaBan"].ToString()) ? dt.Rows[i]["PT_MV_PTT_GiaBan"].ToString() : "0");
                        exSheet.Cells[12 + i, 40] = PT_MV_PTT_GiaBan.ToString();
                        Tong_PT_MV_PTT_GiaBan += PT_MV_PTT_GiaBan;

                        decimal PT_MV_PTT_GiamGia = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_MV_PTT_GiamGia"].ToString()) ? dt.Rows[i]["PT_MV_PTT_GiamGia"].ToString() : "0");
                        exSheet.Cells[12 + i, 41] = PT_MV_PTT_GiamGia.ToString();
                        Tong_PT_MV_PTT_GiamGia += PT_MV_PTT_GiamGia;

                        decimal PT_MV_PTT_ThucThu = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_MV_PTT_ThucThu"].ToString()) ? dt.Rows[i]["PT_MV_PTT_ThucThu"].ToString() : "0");
                        exSheet.Cells[12 + i, 42] = PT_MV_PTT_ThucThu.ToString();
                        Tong_PT_MV_PTT_ThucThu += PT_MV_PTT_ThucThu;

                        decimal PT_PTN_GiaBan = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_PTN_GiaBan"].ToString()) ? dt.Rows[i]["PT_PTN_GiaBan"].ToString() : "0");
                        exSheet.Cells[12 + i, 43] = PT_PTN_GiaBan.ToString();
                        Tong_PT_PTN_GiaBan += PT_PTN_GiaBan;

                        decimal PT_PTN_GiamGia = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_PTN_GiamGia"].ToString()) ? dt.Rows[i]["PT_PTN_GiamGia"].ToString() : "0");
                        exSheet.Cells[12 + i, 44] = PT_PTN_GiamGia.ToString();
                        Tong_PT_PTN_GiamGia += PT_PTN_GiamGia;

                        decimal PT_Thu_PTN = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_Thu_PTN"].ToString()) ? dt.Rows[i]["PT_Thu_PTN"].ToString() : "0");
                        exSheet.Cells[12 + i, 45] = PT_Thu_PTN.ToString();
                        Tong_PT_Thu_PTN += PT_Thu_PTN;

                        decimal PT_PTN_BaoHanh = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_PTN_BaoHanh"].ToString()) ? dt.Rows[i]["PT_PTN_BaoHanh"].ToString() : "0");
                        exSheet.Cells[12 + i, 46] = PT_PTN_BaoHanh.ToString();
                        Tong_PT_PTN_BaoHanh += PT_PTN_BaoHanh;

                        decimal PT_PTN_ThienChi = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_PTN_ThienChi"].ToString()) ? dt.Rows[i]["PT_PTN_ThienChi"].ToString() : "0");
                        exSheet.Cells[12 + i, 47] = PT_PTN_ThienChi.ToString();
                        Tong_PT_PTN_ThienChi += PT_PTN_ThienChi;

                        decimal PT_PTN_KhuyenMai = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_PTN_ThienChi"].ToString()) ? dt.Rows[i]["PT_PTN_KhuyenMai"].ToString() : "0");
                        exSheet.Cells[12 + i, 48] = PT_PTN_KhuyenMai.ToString();
                        Tong_PT_PTN_KhuyenMai += PT_PTN_KhuyenMai;

                        decimal PT_MV_PTN_GiaBan = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_MV_PTN_GiaBan"].ToString()) ? dt.Rows[i]["PT_MV_PTN_GiaBan"].ToString() : "0");
                        exSheet.Cells[12 + i, 49] = PT_MV_PTN_GiaBan.ToString();
                        Tong_PT_MV_PTN_GiaBan += PT_MV_PTN_GiaBan;

                        decimal PT_MV_PTN_GiamGia = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_MV_PTN_GiamGia"].ToString()) ? dt.Rows[i]["PT_MV_PTN_GiamGia"].ToString() : "0");
                        exSheet.Cells[12 + i, 50] = PT_MV_PTN_GiamGia.ToString();
                        Tong_PT_MV_PTN_GiamGia += PT_MV_PTN_GiamGia;

                        decimal PT_MV_PTN_ThucThu = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_MV_PTN_ThucThu"].ToString()) ? dt.Rows[i]["PT_MV_PTN_ThucThu"].ToString() : "0");
                        exSheet.Cells[12 + i, 51] = PT_MV_PTN_ThucThu.ToString();
                        Tong_PT_MV_PTN_ThucThu += PT_MV_PTN_ThucThu;

                        decimal PT_Thu_PTTM = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_Thu_PTTM"].ToString()) ? dt.Rows[i]["PT_Thu_PTTM"].ToString() : "0");
                        exSheet.Cells[12 + i, 52] = PT_Thu_PTTM.ToString();
                        Tong_PT_Thu_PTTM += PT_Thu_PTTM;

                        decimal PT_Chi_PTTM = decimal.Parse(!string.IsNullOrEmpty(dt.Rows[i]["PT_Chi_PTTM"].ToString()) ? dt.Rows[i]["PT_Chi_PTTM"].ToString() : "0");
                        exSheet.Cells[12 + i, 53] = PT_Chi_PTTM.ToString();
                        Tong_PT_Chi_PTTM += PT_Chi_PTTM;

                        decimal PT_Lai_PTTM = decimal.Parse(dt.Rows[i]["PT_Lai_PTTM"].ToString());
                        exSheet.Cells[12 + i, 54] = PT_Lai_PTTM.ToString();
                        Tong_PT_Lai_PTTM += PT_Lai_PTTM;

                        decimal DSPT_CoThuTien = decimal.Parse(dt.Rows[i]["DS_PT_CoThuTien"].ToString());//PT_Thu_Nhot + PT_MV_Nhot_ThucThu + PT_Thu_PTT + PT_MV_PTT_ThucThu + PT_Thu_PTN + PT_MV_PTN_ThucThu + PT_Lai_PTTM;
                        exSheet.Cells[12 + i, 55] = DSPT_CoThuTien.ToString();
                        Tong_DSPT_CoThuTien += DSPT_CoThuTien;

                        decimal DSPT_KhongThuTien = decimal.Parse(dt.Rows[i]["DSPT_KhongThuTien"].ToString()); //PT_GiamGia_Nhot + PT_Nhot_BaoHanh + PT_Nhot_ThienChi + PT_Nhot_KhuyenMai + PT_MV_Nhot_GiamGia + PT_PTT_GiamGia + PT_PTT_BaoHanh + PT_PTT_ThienChi + PT_PTT_KhuyenMai + PT_MV_PTT_GiamGia + PT_PTN_GiamGia + PT_PTN_BaoHanh + PT_PTN_ThienChi + PT_PTN_KhuyenMai + PT_MV_PTN_GiamGia + PT_Chi_PTTM;
                        exSheet.Cells[12 + i, 56] = DSPT_KhongThuTien.ToString();
                        Tong_DSPT_KhongThuTien += DSPT_KhongThuTien;

                        decimal TongDSPT = DSPT_CoThuTien + DSPT_KhongThuTien;
                        exSheet.Cells[12 + i, 57] = TongDSPT.ToString();
                        Tong_TongDSPT += TongDSPT;
                    }
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[12 + dt.Rows.Count, 1], exSheet.Cells[12 + dt.Rows.Count, 57]);
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    rng.Font.Bold = true;
                    exSheet.Cells[12 + dt.Rows.Count, 1] = "Tổng cộng:";
                    exSheet.Cells[12 + dt.Rows.Count, 2] = "";

                    exSheet.Cells[12 + dt.Rows.Count, 3] = Tong_DV_SoLuotRB.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 4] = Tong_DV_SoLuotKTDK.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 5] = Tong_SoLuotSuaChuaNgoaiKTDK.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 6] = Tong_DV_TongLuotXe.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 7] = Tong_ThoiGianRB.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 8] = Tong_ThoiGianKTDK.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 9] = Tong_DV_ThoiGianSua.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 10] = Tong_TongThoiGianSua.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 11] = Tong_DV_DoanhSoRB.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 12] = Tong_DV_DoanhSoKTDK.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 13] = Tong_DV_ThuGCN.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 14] = Tong_DV_ChiGCN.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 15] = Tong_DV_LaiGCN.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 16] = Tong_DV_SC_GiaBan.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 17] = Tong_DV_SC_GiamGia.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 18] = Tong_DV_SC_ThucThu.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 19] = Tong_DV_SuaChua_BaoHanh.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 20] = Tong_DV_SuaChua_ThienChi.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 21] = Tong_DV_SuaChua_KhuyenMai.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 22] = Tong_DS_DV_CoThuTien.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 23] = Tong_DS_DV_KhongThuTien.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 24] = Tong_TongDSDV.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 25] = Tong_PT_GiaBan_Nhot.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 26] = Tong_PT_GiamGia_Nhot.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 27] = Tong_PT_Thu_Nhot.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 28] = Tong_PT_Nhot_BaoHanh.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 29] = Tong_PT_Nhot_ThienChi.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 30] = Tong_PT_Nhot_KhuyenMai.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 31] = Tong_PT_MV_Nhot_GiaBan.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 32] = Tong_PT_MV_Nhot_GiamGia.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 33] = Tong_PT_MV_Nhot_ThucThu.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 34] = Tong_PT_PTT_GiaBan.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 35] = Tong_PT_PTT_GiamGia.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 36] = Tong_PT_Thu_PTT.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 37] = Tong_PT_PTT_BaoHanh.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 38] = Tong_PT_PTT_ThienChi.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 39] = Tong_PT_PTT_KhuyenMai.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 40] = Tong_PT_MV_PTT_GiaBan.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 41] = Tong_PT_MV_PTT_GiamGia.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 42] = Tong_PT_MV_PTT_ThucThu.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 43] = Tong_PT_PTN_GiaBan.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 44] = Tong_PT_PTN_GiamGia.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 45] = Tong_PT_Thu_PTN.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 46] = Tong_PT_PTN_BaoHanh.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 47] = Tong_PT_PTN_ThienChi.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 48] = Tong_PT_PTN_KhuyenMai.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 49] = Tong_PT_MV_PTN_GiaBan.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 50] = Tong_PT_MV_PTN_GiamGia.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 51] = Tong_PT_MV_PTN_ThucThu.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 52] = Tong_PT_Thu_PTTM.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 53] = Tong_PT_Chi_PTTM.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 54] = Tong_PT_Lai_PTTM.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 55] = Tong_DSPT_CoThuTien.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 56] = Tong_DSPT_KhongThuTien.ToString();

                    exSheet.Cells[12 + dt.Rows.Count, 57] = Tong_TongDSPT.ToString();

                    exBook.SaveAs(saveFileExcel.FileName, ComExcel.XlFileFormat.xlWorkbookNormal, null, null, false, false, ComExcel.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false);
                    DialogResult dlg = MessageBox.Show("File báo cáo được lưu ở đường dẫn sau: " + saveFileExcel.FileName + Environment.NewLine + "Bạn có muốn mở file không ?", "Thông Báo", MessageBoxButtons.YesNo);
                    if (dlg == DialogResult.Yes)
                    {
                        // hien thi chuong trinh excel
                        exApp.Visible = true;
                        exBook.ReadOnlyRecommended = false;
                    }
                    else
                    {
                        exBook.Close(false, false, false);
                        exApp.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
                    }
                }
            }
        }
        private void BC_10_11(int type)
        {
            DataTable dt = new DataTable();
            if (type == 10)
                dt = PT_ServiceBillManager.CreateInstant().Report10(fromDate2.Value, toDate2.Value);
            else if (type == 11)
                dt = PT_ServiceBillManager.CreateInstant().Report11(fromDate2.Value, toDate2.Value);

            decimal Tong_SoLuong = 0;
            decimal Tong_ThanhTienVon= 0;
            decimal Tong_ThanhTienBan = 0;
            if (type == 10)
                saveFileExcel.FileName = "BC10_DanhSachChiTietPhuTungKhuyenMai_" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm") + ".xls";
            else if (type==11)
                saveFileExcel.FileName = "BC11_DanhSachChiTietPhuTungBaoHanh_" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm") + ".xls";
            saveFileExcel.DefaultExt = "xls";
            saveFileExcel.Filter = "Excel 2003 Files(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx|All Files|*.*";
            saveFileExcel.AddExtension = true;
            saveFileExcel.RestoreDirectory = true;
            saveFileExcel.Title = "Chọn đường dẫn lưu file";
            saveFileExcel.InitialDirectory = @"C:/";
            if (saveFileExcel.ShowDialog() == DialogResult.OK)
            {
                if (saveFileExcel.FileName.Split('\\')[saveFileExcel.FileName.Split('\\').Length - 1] == ".xls")
                {
                    MessageBox.Show("Nhập tên file cần lưu");
                }
                else
                {
                    // Khoi dong Excel
                    ComExcel.Application exApp = new ComExcel.Application();

                    //// them file temp xls moi
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);

                    // Mo mot file Excel co san
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);
                    string templatePath="";
                    if (type == 10)
                        templatePath = "/ExcelTemplate/BC10.xls";
                    else if (type == 11)
                        templatePath = "/ExcelTemplate/BC11.xls";
                    ComExcel.Workbook exBook = exApp.Workbooks.Add(Application.StartupPath + templatePath);

                    // Lay sheet 1
                    ComExcel.Worksheet exSheet = (ComExcel.Worksheet)exBook.Worksheets[1];
                    //exSheet.Activate();

                    // dia chi 
                    DataTable headdt = PT_ServiceBillManager.CreateInstant().GetReportHead(SessionLogin.UserId).Tables[0];
                    exSheet.Cells[3, 1] = headdt.Rows[0]["HEAD_ADD"];

                    //tu ngay- den ngay - dong 6 , cot 5
                    exSheet.Cells[6, 5] = "Từ ngày: " + fromDate2.Value.ToString("dd/MM/yyyy") + " đến ngày: " + toDate2.Value.ToString("dd/MM/yyyy");

                    ////cau hình chung
                    //ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 1], exSheet.Cells[8 + dt.Rows.Count, HondaTypeList.Count]);
                    //rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    //rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    //rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    //rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[9, 1], exSheet.Cells[dt.Rows.Count + 9, 8]);
                    rng.Font.Size = 9;
                    rng.WrapText = true;
                    // đổ dữ liệu
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[9 + i, 1], exSheet.Cells[9 + i, 8]);
                        rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                        rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;

                        exSheet.Cells[9 + i, 1] = (i + 1).ToString();
                        exSheet.Cells[9 + i, 2] = dt.Rows[i]["Part_no"].ToString();
                        exSheet.Cells[9 + i, 3] = dt.Rows[i]["Part_Name"].ToString();
                        exSheet.Cells[9 + i, 4] = dt.Rows[i]["SL"].ToString();
                        exSheet.Cells[9 + i, 5] = dt.Rows[i]["DGVTB"].ToString();
                        exSheet.Cells[9 + i, 6] = dt.Rows[i]["TTV"].ToString();
                        exSheet.Cells[9 + i, 7] = dt.Rows[i]["DGB"].ToString();
                        exSheet.Cells[9 + i, 8] = dt.Rows[i]["TTB"].ToString();

                        decimal SoLuong = decimal.Parse(dt.Rows[i]["SL"].ToString());
                        Tong_SoLuong += SoLuong;
                        
                        decimal TienVon = decimal.Parse(dt.Rows[i]["TTV"].ToString());
                        Tong_ThanhTienVon += TienVon;
                        if (!string.IsNullOrEmpty(dt.Rows[i]["TTB"].ToString()))
                        {
                            decimal TienBan = decimal.Parse(dt.Rows[i]["TTB"].ToString());
                            Tong_ThanhTienBan += TienBan;
                        }   
                    }
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[9 + dt.Rows.Count, 1], exSheet.Cells[9 + dt.Rows.Count, 8]);
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    rng.Font.Bold = true;
                    exSheet.Cells[9 + dt.Rows.Count, 2] = "Tổng cộng";
                    exSheet.Cells[9 + dt.Rows.Count, 4] = Tong_SoLuong.ToString();
                    exSheet.Cells[9 + dt.Rows.Count, 6] = Tong_ThanhTienVon.ToString();
                    exSheet.Cells[9 + dt.Rows.Count, 8] = Tong_ThanhTienBan.ToString();
                    exBook.SaveAs(saveFileExcel.FileName, ComExcel.XlFileFormat.xlWorkbookNormal, null, null, false, false, ComExcel.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false);
                    DialogResult dlg = MessageBox.Show("File báo cáo được lưu ở đường dẫn sau: " + saveFileExcel.FileName + Environment.NewLine + "Bạn có muốn mở file không ?", "Thông Báo", MessageBoxButtons.YesNo);
                    if (dlg == DialogResult.Yes)
                    {
                        // hien thi chuong trinh excel
                        exApp.Visible = true;
                        exBook.ReadOnlyRecommended = false;
                    }
                    else
                    {
                        exBook.Close(false, false, false);
                        exApp.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
                    }
                }
            }
        }
        private void BC_12()
        {
            DataTable dt = PT_ServiceBillManager.CreateInstant().Report12(fromDate2.Value, toDate2.Value);

            decimal Tong_GiaBan = 0;
            decimal Tong_GiamGia = 0;
            decimal Tong_ThucThu = 0;
            decimal Tong_BaoHanh = 0;
            decimal Tong_ThienChi = 0;
            decimal Tong_KhuyenMai = 0;
            decimal Tong_DSPK = 0;
            saveFileExcel.FileName = "BC12_DoanhSoPhuKienTrangTriCuaKyThuatVienChiTiet_" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm") + ".xls";
            saveFileExcel.DefaultExt = "xls";
            saveFileExcel.Filter = "Excel 2003 Files(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx|All Files|*.*";
            saveFileExcel.AddExtension = true;
            saveFileExcel.RestoreDirectory = true;
            saveFileExcel.Title = "Chọn đường dẫn lưu file";
            saveFileExcel.InitialDirectory = @"C:/";
            if (saveFileExcel.ShowDialog() == DialogResult.OK)
            {
                if (saveFileExcel.FileName.Split('\\')[saveFileExcel.FileName.Split('\\').Length - 1] == ".xls")
                {
                    MessageBox.Show("Nhập tên file cần lưu");
                }
                else
                {
                    // Khoi dong Excel
                    ComExcel.Application exApp = new ComExcel.Application();

                    //// them file temp xls moi
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);

                    // Mo mot file Excel co san
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);
                    string templatePath = "/ExcelTemplate/BC12.xls";
                    ComExcel.Workbook exBook = exApp.Workbooks.Add(Application.StartupPath + templatePath);

                    // Lay sheet 1
                    ComExcel.Worksheet exSheet = (ComExcel.Worksheet)exBook.Worksheets[1];
                    //exSheet.Activate();

                    // dia chi 
                    DataTable headdt = PT_ServiceBillManager.CreateInstant().GetReportHead(SessionLogin.UserId).Tables[0];
                    exSheet.Cells[3, 1] = headdt.Rows[0]["HEAD_ADD"];

                    //tu ngay- den ngay - dong 6 , cot 1
                    exSheet.Cells[6, 1] = "Từ ngày: " + fromDate2.Value.ToString("dd/MM/yyyy") + " đến ngày: " + toDate2.Value.ToString("dd/MM/yyyy");

                    ////cau hình chung
                    //ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 1], exSheet.Cells[8 + dt.Rows.Count, HondaTypeList.Count]);
                    //rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    //rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    //rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    //rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[10, 1], exSheet.Cells[dt.Rows.Count + 10, 10]);
                    rng.Font.Size = 9;
                    rng.WrapText = true;
                    // đổ dữ liệu
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[10 + i, 1], exSheet.Cells[10 + i, 10]);
                        rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                        rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;

                        exSheet.Cells[10 + i, 1] = (i + 1).ToString();
                        exSheet.Cells[10 + i, 2] = dt.Rows[i]["hotennv"].ToString();
                        exSheet.Cells[10 + i, 3] = dt.Rows[i]["maktv"].ToString();
                        exSheet.Cells[10 + i, 4] = dt.Rows[i]["giaban"].ToString();
                        exSheet.Cells[10 + i, 5] = dt.Rows[i]["giamgia"].ToString();
                        exSheet.Cells[10 + i, 6] = dt.Rows[i]["thucthu"].ToString();
                        exSheet.Cells[10 + i, 7] = dt.Rows[i]["baohanh"].ToString();
                        exSheet.Cells[10 + i, 8] = dt.Rows[i]["thienchi"].ToString();
                        exSheet.Cells[10 + i, 9] = dt.Rows[i]["khuyenmai"].ToString();
                        exSheet.Cells[10 + i, 10] = dt.Rows[i]["tongPKTT"].ToString();

                        decimal Giaban = decimal.Parse(dt.Rows[i]["giaban"].ToString());
                        Tong_GiaBan += Giaban;

                        decimal GiamGia = decimal.Parse(dt.Rows[i]["giamgia"].ToString());
                        Tong_GiamGia += GiamGia;

                        decimal ThucThu = decimal.Parse(dt.Rows[i]["thucthu"].ToString());
                        Tong_ThucThu += ThucThu;

                        decimal Baohanh = decimal.Parse(dt.Rows[i]["baohanh"].ToString());
                        Tong_BaoHanh += Baohanh;

                        decimal ThienChi = decimal.Parse(dt.Rows[i]["thienchi"].ToString());
                        Tong_ThienChi += ThienChi;

                        decimal KhuyenMai = decimal.Parse(dt.Rows[i]["khuyenmai"].ToString());
                        Tong_KhuyenMai += KhuyenMai;

                        decimal tongPKTT = decimal.Parse(dt.Rows[i]["tongPKTT"].ToString());
                        Tong_DSPK += tongPKTT;
                    }
                    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[10 + dt.Rows.Count, 1], exSheet.Cells[10 + dt.Rows.Count, 10]);
                    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    rng.Font.Bold = true;
                    exSheet.Cells[10 + dt.Rows.Count, 1] = "TỔNG";
                    exSheet.Cells[10 + dt.Rows.Count, 4] = Tong_GiaBan.ToString();
                    exSheet.Cells[10 + dt.Rows.Count, 5] = Tong_GiamGia.ToString();
                    exSheet.Cells[10 + dt.Rows.Count, 6] = Tong_ThucThu.ToString();
                    exSheet.Cells[10 + dt.Rows.Count, 7] = Tong_BaoHanh.ToString();
                    exSheet.Cells[10 + dt.Rows.Count, 8] = Tong_ThienChi.ToString();
                    exSheet.Cells[10 + dt.Rows.Count, 9] = Tong_KhuyenMai.ToString();
                    exSheet.Cells[10 + dt.Rows.Count, 10] = Tong_DSPK.ToString();
                    exBook.SaveAs(saveFileExcel.FileName, ComExcel.XlFileFormat.xlWorkbookNormal, null, null, false, false, ComExcel.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false);
                    DialogResult dlg = MessageBox.Show("File báo cáo được lưu ở đường dẫn sau: " + saveFileExcel.FileName + Environment.NewLine + "Bạn có muốn mở file không ?", "Thông Báo", MessageBoxButtons.YesNo);
                    if (dlg == DialogResult.Yes)
                    {
                        // hien thi chuong trinh excel
                        exApp.Visible = true;
                        exBook.ReadOnlyRecommended = false;
                    }
                    else
                    {
                        exBook.Close(false, false, false);
                        exApp.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
                    }
                }
            }
        }

        private void RunReport()
        {
            if (cmbBaoCao.SelectedValue != null && !string.IsNullOrEmpty(cmbBaoCao.SelectedValue.ToString()))
            {
                switch (cmbBaoCao.SelectedValue.ToString())
                {
                    case "BC1":
                        BC_DanhSachKhachHangDenSuaXe();
                        break;
                    case "BC2":
                        BC_DoanhSoDichVuHangKyChiTiet();
                        break;
                    case "BC3":
                        BC_DoanhSoDichVuHangKy();
                        break;
                    case "BC4":
                        BC_TongHopSoLuongXeDenCuaHang();
                        break;
                    case "BC5":
                        BC_TongHopSoLanDenCuaKhachHang();
                        break;
                    case "BC6":
                        BC_DoanhThuDichVuPhuTungNhot();
                        break;
                    case "BC7":
                        BC_SoLuongXeDenKhuVucDichVuTrongNgay();
                        break;
                    case "BC8":
                        BC_KeToan_TongHopDoanhThuDichVuPhuTungNhot();
                        break;
                    case "BC9":
                        BC_KeToan_DoanhThuDichVuPhuTungNhotChiTiet();
                        break;
                    case "BC10":
                        BC_10_11(10);
                        break;
                    case "BC11":
                        BC_10_11(11);
                        break;
                    case "BC12":
                        BC_12();
                        break;
                }
            }
        }
        private void btBaoCao_Click(object sender, EventArgs e)
        {
            ShowStatusBar("Đang xuất dữ liệu excel");
            RunReport();
            EndShowStatusBar();
        }
        private static readonly ILog _logger = LogManager.GetLogger(typeof(ucReport).Name);
        private void btUpdateData_Click(object sender, EventArgs e)
        {
            ShowStatusBar("Đang cập nhật dữ liệu");
            btUpdateData.Enabled = false;
            Thread mainThread = new Thread(new ThreadStart(UpdateData));
            mainThread.Start();
        }
        public void UpdateData()
        {
            try
            {
                PT_ServiceBillManager serviceBillM = new PT_ServiceBillManager();
                string message="";
                serviceBillM.Update_ChiTietDoanhSoTheoAgent(out message);
                MessageBox.Show(message);
                this.Invoke((MethodInvoker)delegate
                {
                    EndShowStatusBar();
                    btUpdateData.Enabled = true;
                });
            }
            catch (Exception ex)
            {
                _logger.Error(ex);
                this.Invoke((MethodInvoker)delegate
                {
                    EndShowStatusBar();
                    btUpdateData.UseWaitCursor = false;
                    EndShowStatusBar();
                    btUpdateData.Enabled = true;
                });
                MessageBox.Show("Thao tác lỗi. Vui lòng thử lại!");
            }
        }
    }
}
